Are there any other ways to do this? While it works it is extremely resource intensive for large data sets.
Normally, the Vlookup function can help you to find the data from top to bottom to get the first matching value from the list. But, sometimes, you need to vlookup from bottom to top to extract the last corresponding value. Do you have any good ideas to deal with this task in Excel?
Vlookup and return matching value from top to bottom:
With Kutools for Excel's Look for a value in list feature, you can quickly find and return the matching value from top to bottom in a table list.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
To vlookup matching value from bottom to top, the following LOOKUP formula can help you, please do as follows:
Please enter this formula: =LOOKUP(2,1/($A$2:$A$19=D2),$B$2:$B$19) into a blank cell where you want to get the result, and press Enter key, the last corresponding value will be returned at once, see screenshot:
Note: In the above formula: A2:A19 indicates the column that you are looking for, D2 is the criteria which you want to return its relative data and B2:B19 is the list that contains the value you want to return.
The above formula can help you vlookup data from bottom to top, and this section, I will talk about how to vlookup value from top to bottom. If you have Kutools for Excel, with its Look for a value in list utility, you can quickly return the first matching value from top to bottom.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. Get it Now .|
After installing Kutools for Excel, please do as follows:
1. Click a blank cell where you want to put the result.
2. Then click Kutools > Formulas > Look for a value in list, see screenshot:
3. In the Formula Helper dialog box:
(1.) Click first button beside Table_array to select the data range which you want to use;
(2.) Click second button beside Look_value to select the criteria that you want to return its corresponding value;
(3.) Click third button beside Column to select the column contains the value you want to return.
4. After finishing the settings, click Ok button, the first matching value from top has been displayed as following screenshot shown: