# VLookup - find highest Value...

1. ## VLookup - find highest Value...

Hi All, I was hoping someone could help me out with this one. I'm not sure that a form of Vlookup would be correct or not but here it goes...

I'm using a vlookup of account numbers to find a date. Problem is, some account numbers have multiple dates, so there are multiple entries. Right now, my Vlookup will pick up the first date in the list, but I need it to pick up the most recent date. Any ideas?

For example
Account Date
1001 1/2/2008
2001 1/5/2008
3001 1/7/2008
2001 1/9/2008
2001 1/12/2008
4001 1/23/2008

so if I lookup account number 2001, it will return 1/5/2008 but I want it to return 1/12/2008.

Jason  Register To Reply

2. Assuming dates are entered in ascending order... i.e every date entry for the account number gets later

Try:

=MAX(IF(\$A\$1:\$A\$10=X1,\$B\$1:\$B\$10))

Where X1 contains the lookup account number and A1:B10 is your lookup table.

This formula is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear  Register To Reply

3. Assuming dates are entered in ascending order... i.e every date entry for the account number gets later

Try:

=MAX(IF(\$A\$1:\$A\$10=X1,\$B\$1:\$B\$10))

Where X1 contains the lookup account number and A1:B10 is your lookup table.

This formula is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear  Register To Reply

4. thanks - is there anyway this can be done without an array formula? I have many rows of data and these type of formula tend to really slow down my spreadsheets.  Register To Reply

5. Not sure how much faster, if at all, but this is an non-array entered formula:

=LOOKUP(2,1/(\$A\$1:\$A\$10=X1),\$B\$1:\$B\$10)

This finds the last entry in the column that matches X1 and returns corresponding item from B1:B10  Register To Reply

6. ## thanks again - very helpful. Hope you don't mind my persistence but promise this will be my last question... often time my data will get filtered and sorted, so the last entry is not neccessarily the most recent date. I suppose I could filter every time so that this will work, but is there a way to modify the non array formula so it will return the Max date (or the most recent) instead of the last date in the list?

Jason  Register To Reply

7. Try:

=MAX(INDEX((\$A\$1:\$A\$10=X1)*(\$B1:\$B\$10),0))  Register To Reply