INDEX Formula

[Dhoang25]

Hi,

I have an excel spreadsheet with column A as Loan Number, column H as ID, and column Y as # of Days (loan age). Here is an issue; one of the employees has two IDs (current and former ID). What I need is the OLDEST Loan number in column A of the two IDs. For example, if the 1st ID has the oldest loan of 10 days and the 2nd ID has the oldest loan of 20 days, give me the Loan Number of the 2nd ID. The formula that I currently use doesn't work. Please see Dummy Workbook. Thanks

=INDEX('Detail Info'!$A$2:$A$2000,MATCH(1,('Detail Info'!$H$2:$H$2000=D3,D4)*(MAX(IF(('Detail Info'!$H$2:$H$2000=D3,D4),'Detail Info'!$Y$2:$Y$2000))='Detail Info'!$Y$2:$Y$2000),0))

[NBVC]

Try:

In D5:

Please Login or Register  to view this content.

and in D6:

Please Login or Register  to view this content.

each confirmed with CTRL+SHIFT+ENTER and copied across.

[watersev]

see attachment file

[Dhoang25]

Awesome! Thanks a bunch!

[Dhoang25]

Watersev,

I tried NBVC's suggestion and it worked. Not sure why your formula doesn't work but thanks for helping. Much appreciated!!!