How to Use the XMATCH Function in Excel for Reverse Lookups (Bottom-to-Top Searching)

For decades, Excel users relied on the MATCH function (usually paired with INDEX) to find exactly which row a specific piece of data lived on. However, the legacy MATCH function has a strict limitation: it always searches from top to bottom. It starts at row 1 and stops the exact millisecond it finds a match.

This is a major problem if you maintain a continuous log (like an inventory tracker, an attendance sheet, or a bank ledger) where you constantly add new entries to the bottom of the spreadsheet. If a customer buys “Product A” on January 1st, and then buys “Product A” again on December 31st, a standard MATCH function will only ever report the January 1st transaction. Finding the most recent (bottom-most) entry was practically impossible without writing convoluted, laggy array formulas.

Microsoft solved this entirely with the introduction of the XMATCH function, which natively supports reverse, bottom-to-top searching.

The XMATCH Syntax

The XMATCH function is designed to be much simpler and more powerful than its predecessor. The basic syntax requires three arguments:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

• lookup_value: What are you looking for? (e.g., “Product A”)
• lookup_array: Where are you looking? (e.g., Column B)
• match_mode: 0 for an exact match (this is the default, so you can usually skip it).
• search_mode: This is the magic argument. 1 searches top-to-bottom. -1 searches bottom-to-top.

Step 1: The Scenario

Imagine a massive sales log. Column A contains Dates, and Column B contains the Product Name. You want to find the exact row number of the most recent sale of “Widget XYZ”.

Step 2: Writing the Reverse Lookup Formula

1. Click on the cell where you want the answer to appear.
2. Begin typing the formula, defining what you are looking for and where to look:

   =XMATCH("Widget XYZ", B:B, 

3. Next, we must define the match mode. We want an exact match, so type 0:

   0, 

4. Finally, we define the search mode. To force Excel to start at row 1,048,576 and scan upwards, type -1:

   -1)

Step 3: Execution and Pairing with INDEX

Your complete formula looks like this:

=XMATCH("Widget XYZ", B:B, 0, -1)

Press Enter. Excel will instantly scan from the bottom of the sheet upwards and return the row number of the very last occurrence of “Widget XYZ” (for example, row 4,052).

While finding the row number is useful, it is usually paired with the INDEX function to extract data from a neighboring column (like finding the Date of that last transaction in Column A).

To extract the date of the most recent sale, wrap your XMATCH inside an INDEX function like this:

=INDEX(A:A, XMATCH("Widget XYZ", B:B, 0, -1))

This single, elegant formula tells Excel: “Scan Column B from the bottom up. Find the last time Widget XYZ was sold. Give me the row number. Now, look at Column A on that exact same row, and print the date here.”