MATCH or VLOOKUP starting from the end of the range

excel worksheet-function
assylias picture assylias · Mar 6, 2012 · Viewed 20.6k times · Source

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...).

Answer

lori_m picture lori_m · Mar 6, 2012

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.

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More