Searching from Bottom to Top with Match

**tomlancaster** · 08-21-2009, 07:10 AM

Hello

I am using MATCH to find a value in a list of data on another sheet.

However, there are multiple matches and the formula only returns the first value. Is there a way I can tell excel to return the latest value at the bottom of the list and not the top?

My formula at the moment is:

Please Login or Register  to view this content.

Your assistance will be greatly appreciated !

**martindwilson** · 08-21-2009, 07:30 AM

to use row number of last value in and index match use lookup instead
=INDEX(A1:B100, LOOKUP(2,1/(A1:A100=10),ROW(A1:A100)),2)

**NBVC** · 08-21-2009, 07:52 AM

or just:
=LOOKUP(2,1/(A1:A100=10),B1:B100)

**tomlancaster** · 08-21-2009, 07:57 AM

Thanks for your reply. I've tried adapting the formula but I get #NAME #NA

**NBVC** · 08-21-2009, 08:02 AM

Using your references from your sample formula...did you try something like:

=LOOKUP(2,1/(Updates!A1:A100=A3),Updates!D1:D100)

Note you cannot have full column references in this formula if you are not using XL2007.

**tomlancaster** · 08-21-2009, 09:09 AM

That works!! God knows what I did..? Thanks

**NBVC** · 08-21-2009, 09:13 AM

Please don't forget to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save