Function Problems

How to Find the Last Matching Result in Excel with XMATCH or XLOOKUP

Huda Qurayshi Oct 1, 2026 868 views

Question details

The user needs to retrieve the final matching value for a specific lookup criteria when multiple records exist, while properly handling potential data type mismatches such as text versus numbers.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for the most recent or last occurrence of a record in a dataset that contains duplicate lookup values.
Observed behavior: Standard lookup functions return the first match by default, requiring a specialized reverse search approach to find the final matching value.

Before you start

Ensure your lookup values and source data share the exact same format (either both text or both numbers) to avoid unexpected #N/A match errors.

Solution 1Recommended

Use XLOOKUP to Perform a Reverse Search

XLOOKUP has a built-in search mode argument that allows you to search from the bottom of your dataset to the top, making it the most straightforward way to find the last match.

By modifying the search mode argument to -1, XLOOKUP will scan your array in reverse order, stopping at the first match it finds from the bottom, which is effectively the last matching result in your data.

Select the destination cell

Click on the cell where you want the final matched result to be displayed.

Enter the XLOOKUP formula

Type the formula =XLOOKUP(lookup_value, lookup_array, return_array, "", 0, -1). Ensure you replace the placeholder text with your actual cell references.

Apply the reverse search mode

The crucial part of this formula is the -1 at the very end. This tells the function to search last-to-first. Press Enter to retrieve the result.

Handling Blank Cells: You can add a nested FILTER function inside the XLOOKUP if you need to ignore blank cells during your reverse search.

Solution 2

Combine XMATCH and INDEX for Complex Arrays

If you are already using INDEX for two-dimensional array lookups, XMATCH can provide the row number of the last match.

Advanced Spreadsheets in WPS

Find the Last Match Easily with WPS Spreadsheet

WPS Office provides full support for advanced dynamic array functions like XLOOKUP and XMATCH. You can easily perform reverse searches to find the last matching result in your data without compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
2. Start the XLOOKUP function: Select the target cell, type =XLOOKUP(, and select the cell containing your lookup criteria.
3. Define your arrays: Highlight the lookup array and the return array where your target data resides.
4. Set search mode to reverse: Type ,,0,-1) at the end of the formula and press Enter to seamlessly retrieve the last matching record.

Fully compatible with Microsoft Excel formulas including XLOOKUP, XMATCH, and INDEX.Perform reverse searches and advanced data lookups quickly.Lightweight, fast, and completely free to use for everyday spreadsheet tasks.

Frequently Asked Questions

Why does VLOOKUP only return the first match?

VLOOKUP is designed to search iteratively from the top down and automatically stops at the first exact match it encounters. It lacks a built-in reverse search parameter, which is why modern functions like XLOOKUP or XMATCH are necessary for finding the last match.

How do I fix #N/A errors when searching for numbers stored as text?

Data type mismatches cause exact match lookup functions to fail. You can wrap your lookup value in the NUMBERVALUE function (e.g., NUMBERVALUE(A2)) inside your XMATCH or XLOOKUP formula to force a text string into a numeric format.

Is XLOOKUP available in all versions of Excel?

No, XLOOKUP and XMATCH are only available in Microsoft 365, Excel 2021, and newer versions. If you are using an older version, you must use a combination of the LOOKUP function or an array formula utilizing MAX and ROW.

Can I find the last match using multiple criteria?

Yes, you can use XLOOKUP with multiple criteria by concatenating the lookup values and the lookup arrays using the ampersand (&) symbol, while still applying the -1 search mode argument at the end of the formula.

Excel & Data Problems