XLOOKUP search_mode -1: How to Search Last to First

Most XLOOKUP examples stop at exact match. Real spreadsheets do not. The moment your lookup column contains duplicates, the default behavior becomes a problem because XLOOKUP returns the first match it finds. If you need the latest invoice, the most recent status, or the last recorded price, first match is wrong.

That is what search_mode = -1 is for. It tells XLOOKUP to scan from the bottom of the lookup range back to the top, returning the last match instead of the first.

The Formula

The full XLOOKUP syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

To search last-to-first, use -1 as the sixth argument:

=XLOOKUP(A2, Orders[Customer], Orders[Amount], "Not found", 0, -1)

This looks for the value in A2, searches the Orders[Customer] column from bottom to top, and returns the matching value from Orders[Amount].

When search_mode -1 Is the Right Tool

Use search_mode = -1 when duplicates are expected and the last record is the one you want:

• Latest transaction for a customer or SKU
• Most recent status in an operations tracker
• Last non-empty value in a repeated log table
• Newest price when historic prices are stacked vertically

If the data is sorted chronologically from oldest to newest, last-to-first search is usually the safest way to pull the current record without helper columns.

What People Misunderstand About It

There are three common mistakes:

• Confusing reverse lookup with reverse search. XLOOKUP already supports returning values from a column to the left. That is a separate feature from search_mode = -1. Search mode changes the direction of the scan, not the direction of the returned column.
• Leaving match mode implicit. If you care about exact matching, set match_mode to 0 explicitly. It makes the formula safer to read and review.
• Assuming it fixes bad data. If customer IDs contain trailing spaces or numbers stored as text, last-to-first search still returns the wrong thing or nothing at all. Search direction does not solve data hygiene problems.

The Edge Cases That Matter

This is where AI-generated formulas often look correct but fail in the workbook:

• Blank rows inside the lookup range. The formula still works, but users often assume blanks mean "end of data" when the table actually continues lower down.
• Text vs. number mismatches. 123 and "123" do not always match the way people expect after imports.
• Wrong Excel version. XLOOKUP is not available in Excel 2019 or earlier. If a tool gives you this formula without checking version compatibility, you get a #NAME? error, not a helpful warning.
• Unsorted assumptions. search_mode = -1 does not require sorted data. Binary search modes do. Users sometimes mix these up when copying formulas from generic tutorials.

This is exactly why validation matters. A formula can be syntactically correct and still fail on the real worksheet because the data shape or Excel version was never checked.

Example: Return the Latest Status for an Order

Imagine a log like this:

Order ID    Status
1001        Created
1002        Created
1001        Packed
1001        Shipped

To return the latest status for order 1001:

=XLOOKUP(1001, A2:A5, B2:B5, "Not found", 0, -1)

The result is Shipped, not Created. That is the entire point of last-to-first search.

What to Use If XLOOKUP Is Not Available

If you're supporting Excel 2019 or older, you need a different pattern. The usual fallback is a LOOKUP trick, INDEX/MATCH combination, or helper-column approach. Those work, but they are less readable and easier to get wrong. If your workbook has to be backward-compatible, that constraint should shape the formula before you generate it.