Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MATCH or VLOOKUP starting from the end of the range

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

like image 534
assylias Avatar asked Mar 06 '12 16:03

assylias


People also ask

Does VLOOKUP return the first or last match?

VLOOKUP also assumes by default that the first column in the table array is sorted alphabetically, and suppose your table is not set up that way, VLOOKUP will return the first closest match in the table, which may not be the data you are looking for.

Does VLOOKUP search for the lookup value in the last column of the range?

Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C. The column number in the range that contains the return value.

Does VLOOKUP take the first match?

VLOOKUP will only look for a closest or exact match to a value. It also assumes by default that the first column in the table array is sorted alphabetically. If your table is not sorted that way, VLOOKUP will return the first closest match which in many cases may not be your desired output.

What is Xlookup vs VLOOKUP?

The range for the VLOOKUP includes the entire column, but the XLOOKUP splits the referenced ranges to a range to search and one to find the returned value. Also note that the XLOOKUP used one formula to return two values.


1 Answers

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.

like image 153
lori_m Avatar answered Sep 19 '22 15:09

lori_m