MATCH or VLOOKUP starting from the end of the range

excel, worksheet-function

Solution

With data in A1:B4 and D1="ID2", try entering in E1:

=LOOKUP(2,1/(A1:A4=D1),B1:B4)

Note: LOOKUP returns the last value if the lookup value is larger than any of the values in the lookup range. Any errors are ignored.

Problem

I have a table that looks like this ``` A B ID1 data 123 ID2 data 234 ID1 data 456 ID2 data 567 ``` I am trying to find the best way to retrieve `data 567` for ID2. Using MATCH (with option 0) or VLOOKUP (with option FALSE) on ID2 gives me access to the first record but I want to retrieve the last record. I am using Excel 2010. ps: I'd rather not use VBA or manipulate the data (sorting...).

Original source