Lookup based on lastest date

[jensenleesen]

I am trying do up lookup formula to locate a value based on a latest date of a particular column and corresponding criteria.

Please see example below.
I wish to find the lastest value based on EffectiveDate with the corresponding EmpID.

So the values under the SalaryAnnual (highlighted in red), it should be reflected as 55,000 for EmpID E10001, and 53,250 for EmpID E10002.

I have been trying to use - if max array, but none seem to work!..

EmpID....Name....EffectiveDate....SalaryAnnual...EmpID.....SalaryAnnual
E10001....LSJ.......01/04/2009 ......50,000 ...........E10001....55,000
E10002....CYS......01/04/2009 ......48,000 ...........E10002....53,250
E10001....LSJ.......01/04/2010 ......53,000
E10002....CYS......01/04/2010 ......51,000
E10001....LSJ.......01/05/2010 ......55,000
E10002 ...CYS .....01/05/2010 ......53,250

[shg]

Please Login or Register  to view this content.

The formula in G2 and copied down is

=INDEX($D$2:$D$7, MATCH(F2 & MAX(IF($A$2:$A$7=F2,$C$2:$C$7)), $A$2:$A$7 & $C$2:$C$7, 0))

It MUST be confirmed with Ctrl+Shift+Enter.

You have some trailing blanks in col A that need to be fixed.

[NBVC]

If your dates are listed chronologically for each user as per your sample, then this too may work:

=LOOKUP(2,1/($A$2:$A$7=E2),$D$2:$D$7)

[jensenleesen]

Thanks! =INDEX($D$2:$D$10, MATCH(F2 & MAX(IF($A$2:$A$10=F2,$C$2:$C$10)), $A$2:$A$10 & $C$2:$C$10, 0)) works well for me!

[shg]

I like NBVC's if the dates are indeed in ascending order.