How to vlookup and return the last matching value in Excel?

In Excel, the VLOOKUP function is commonly used to search for and retrieve data from a table. However, by default, "VLOOKUP" only returns the first matching value it finds. What if you need to return the last matching value instead? To achieve this, we use alternative formulas with functions like "LOOKUP", "XLOOKUP", "NDEX", "MATCH" or "Kutools". We’ll also explore how to optimize these methods for better performance and usability.

Vlookup and return the last matching value in Excel

• With LOOKUP functions
• With Kutools for Excel
• With INDEX and MATCH functions
• With XLOOKLUP function(Excel 365/2021 or later)

Vlookup and return the last matching value with LOOKUP function

The "LOOKUP" function is a powerful tool in Excel that can be used to find the last matching value in a dataset.

Please enter the following formula into your specified cell, and then drag the fill handle down to the cells to get the last corresponding value as follows:

=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)

In the formula:

• "A2:A12" is the range containing the lookup column;
• "E2" is the cell containing the value to look up;
• "C2:C12" is the range containing the return values.

Vlookup and return the last matching value with Kutools for Excel

"Kutools for Excel" provides an easy and efficient way to perform advanced lookups, including returning the last matching value from a dataset. Follow these steps to achieve this without complex formulas.

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, please 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.
  

Then, all the last matching items have been returned at once, see screenshot:

Vlookup and return the last matching value with INDEX and MATCH functions

While the traditional VLOOKUP function doesn't support this functionality, you can combine the powerful "INDEX" and "MATCH" functions to achieve it. This method is dynamic, efficient, and works across all Excel versions.

Please enter the following formula into your specified cell, press "Ctrl" + "Shift" + "Enter" key in Excel 2019 and earlier versions, press "Enter" key directly in Excel 365, Excel 2021 and later versions.

=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))

Then drag the fill handle down to the cells to get the last corresponding value as follows:

In the formula:

• "A2:A12" is the range containing the lookup column;
• "E2" is the cell containing the value to look up;
• "C2:C12" is the range containing the return values.

Vlookup and return the last matching value with XLOOKUP function

The XLOOKUP function, introduced in Excel 365, 2021 and later versions, is a powerful and versatile replacement for "VLOOKUP" and "HLOOKUP". One of its key features is the ability to perform lookups in reverse order, making it perfect for finding the last matching value in a dataset.

Please enter the following formula into your specified cell, and then drag the fill handle down to the cells to get the last corresponding value as follows:

=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)

In the formula:

• "E2": The lookup value;
• "A2:A12": The lookup array, i.e., the range where the function searches for the lookup value;
• "C2:C12": The return array, i.e., the range from which the corresponding value is returned;
• ,,: These two commas represent optional arguments for if_not_found and match_mode. We leave them empty in this case.
• "-1": Specifies the search mode to start searching from the bottom of the range.

Returning the last matching value in Excel can be achieved using a variety of methods, depending on your needs and the version of Excel you’re using. Each method has its own strengths, and your choice should depend on the Excel version you have. By mastering one or more of these techniques, you’ll significantly enhance your data management skills and streamline your workflow in Excel. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

More relative articles: