Skip to main content

How to vlookup matching value from bottom to top in Excel?

Author Xiaoyang Last modified

Excel's VLOOKUP function typically searches from top to bottom, returning the first matching value it encounters. However, in some cases, you may need to search from the bottom up. Since VLOOKUP lacks built-in reverse search functionality, you need alternative methods to achieve this.

Vlookup the last matching value from bottom to top in Excel

Vlookup the last matching value from bottom to top with LOOKUP function

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:

=LOOKUP(2,1/($A$2:$A$17=D2),$B$2:$B$17)

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.
Explanation of the formula:
  • $A$2:$A$17=D2: This part checks which cells in the range A2:A17 are equal to the value in cell D2. It returns an array of TRUE (if the condition is met) or FALSE (if not).
  • 1/($A$2:$A$17=D2): This converts the TRUE/FALSE array into 1s (for TRUE) and #DIV/0! errors (for FALSE), because dividing 1 by TRUE gives 1, and dividing by FALSE (which is 0) results in an error.
  • LOOKUP(2, ...): The LOOKUP function searches for the value 2 in the array created in step 2. Since 2 is larger than any 1 in the array, it matches the last 1 (i.e., the last occurrence where the condition is met).
  • $B$2:$B$17: This is the range from which the corresponding value is returned. The function returns the value in B2:B17 that aligns with the last 1 found in step 3.

Vlookup the last matching value from bottom to top with Kutools for Excel

Fortunately, "Kutools for Excel" simplifies this process with its powerful "LOOKUP from Bottom to Top" feature. This tool allows you to quickly find and return the last matching value in a list without requiring complicated formulas.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After installing Kutools for Excel, please do as this:

1. Click "Kutools" > "Super LOOKUP" > "LOOKUP from Bottom to Top", see screenshot:

2. In the "LOOKUP from Bottom to Top" dialog box, do the following operations:

  • Select the lookup value cells and output cells from the "Output Range" and "Lookup values" sections;
  • Then, specify the corresponding items from the "Data range" section.
  • Finally, click "OK" button.

set options in the dialog box

3. All the last matching values will be returned at once, see screenshot:

All the last matching values are returned

Download and free trial Kutools for Excel Now!

Tip:

If you want to replace the #N/A error value with another text value, you just need to click "Options" button and check "Replace #N/A error value with a specified value" option, then type the text you need.
handle error values

Vlookup the last matching value from bottom to top with XLOOKUP function

The XLOOKUP function is a modern replacement for VLOOKUP and HLOOKUP. It simplifies the process of finding the last matching value.

Enter the following formula into a blank cell, and then drag the fill handle down to the cells to get the last corresponding value as follows:

=XLOOKUP(D2, $A$2:$A$17, $B$2:$B$17, , , -1)

Explanation of the formula:
  • D2: The value to search for.
  • $A$2:$A$17: The lookup range.
  • $B$2:$B$17: The return range.
  • -1: Search from the last value to the first (bottom to top).

Conclusion

  • If you’re using Excel 365/2021 or later, the XLOOKUP function is the most efficient and straightforward way to find the last matching value.
  • For older versions of Excel, the LOOKUP function is a reliable alternative, though it requires a bit more understanding of array logic.
  • If you prefer a user-friendly, no-formula approach, Kutools for Excel is an excellent choice, though it comes with the cost of a third-party add-in.

By following these methods, you can efficiently VLOOKUP from bottom to top and find the last matching value in your dataset. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

More relative articles:

  • Vlookup Values Across Multiple Worksheets
  • 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.
  • Use Vlookup Exact And Approximate Match In Excel
  • 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.
  • Vlookup To Return Blank Or Specific Value Instead Of 0 Or N/A
  • 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?
  • Vlookup And Concatenate Multiple Corresponding Values In Excel
  • 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.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!