How to vlookup matching value from bottom to top in Excel?
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 the last matching value from bottom to top with formula
To vlookup matching value from bottom to top, the following LOOKUP formula can help you, please do as follows:
Please enter the below formula into a blank cell where you want to get the result:
Then drag the fill handle down to the cells that you want to get the results, the last corresponding values will be returned at once, see screenshot:
Note: In the above formula: A2:A17 indicates the column that you are looking for,D2 is the criteria which you want to return its relative data and B2:B17 is the list that contains the value you want to return.
Vlookup the last matching value from bottom to top with a useful feature
If you have Kutools for Excel, with its LOOKUP from Bottom to Top feature, you can also solve this task without remembering any formula.
Tips:To apply this LOOKUP from Bottom to Top feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.
In excel, we can easily apply the vlookup function to return the matching values in a single table of a worksheet. But, have you ever considered that how to vlookup value across multiple worksheet? Supposing I have the following three worksheets with range of data, and now, I want to get part of the corresponding values based on the criteria from these three worksheets.
In Excel, vlookup is one of the most important functions for us to search a value in the left-most column of the table and return the value in the same row of the range. But, do you apply the vlookup function successfully in Excel? This article, I will talk about how to use the vlookup function in Excel.
Normally, when you apply the vlookup function to return the corresponding value, if your matching cell is blank, it will return 0, and if your matching value is not found, you will get an error #N/A value as below screenshot shown. Instead of displaying the 0 or #N/A value, how can you make it show blank cell or other specific text value?
Normally, you can vlookup and return a matching value from a range of data by using the Vlookup function, but, have you ever tried to find and return the whole row of data based on specific criteria as following screenshot shown.
As we all known, the Vlookup function in Excel can help us to lookup a value and return the corresponding data in another column, but in general, it can only get the first relative value if there are multiple matching data. In this article, I will talk about how to vlookup and concatenate multiple corresponding values in only one cell or a vertical list.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
The actual value of the "2" is irrelevant, it could be any number greater than 1 and less than infinity.
I believe the second term i.e. "1/($A$2:$A$19=D2)" creates an array by evaluating each cell in the range and if it is equal to D2 (a boolean test) it equates this to 1/TRUE, which as TRUE is 1 => 1. If it doesn't equal D2 it equates this to 1/FALSE, which as FALSE is 0 => infinity or Not a Number.
LOOKUP then takes over and searches for 2 in this array, which of course it can't find as they are either 1 or infinity.
If LOOKUP can't find a match it matches the highest number that is less than or equal to 2. Which is 1.
I'm not quite sure why this turns out to be the last occurrence of 1 though. Maybe LOOKUP (unlike VLOOKUP) always searches upwards?
Hi Please can you explain this formula "=LOOKUP(2,1/($A$2:$A$19=D2),$B$2:$B$19)".
It's worked for what I want to acheive very well, but I do not completely understand how. When typed into Excel, it shows that "2" is the lookup value, but we are actually looking up "D2" in this formula. Also what is "1/" doing on the lookup vector?
Please could you break this formula down to explain how it works?