Menu
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...).
534
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.
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.
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.
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.
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.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
