XLOOKUP Last Match: How to Find It

XLOOKUP by default returns the first match, but you can make it return the last match by setting the optional search_mode argument to -1. This tells Excel to search from the last value to the first, so you get the most recent occurrence in your data. Here is the full syntax and everything you need to know to use it correctly.

Formula

The XLOOKUP function has the following syntax:

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

The arguments you care about for a last match are:

• lookup_value – the value you are searching for (e.g., a product ID).
• lookup_array – the range where you search for that value.
• return_array – the range from which you return the corresponding value.
• match_mode – set to 0 for an exact match (this is the default).
• search_mode – set to -1 to search from last to first.

So the complete formula for a last exact match is:

=XLOOKUP(A2, B:B, C:C, "Not found", 0, -1)

Here, A2 contains the lookup value, B:B is the column with the values to search, C:C is the column with the results, “Not found” is the text to show if no match exists, 0 means exact match, and -1 means search from the bottom up.

Worked Example

Suppose you have a sales log with repeated product IDs and you want the latest price for each product. Your data looks like this:

• Row 2: Product A, $10
• Row 3: Product B, $15
• Row 4: Product A, $12
• Row 5: Product A, $14
• Row 6: Product B, $18

You want the last price for Product A. Using the formula:

=XLOOKUP("Product A", A2:A6, B2:B6, "Not found", 0, -1)

Excel searches from row 6 upward. It finds “Product A” in row 5, so it returns $14. If you had used the default search mode (1), it would have found row 2 and returned $10.

The calculator above works out average pace from total time and distance, which is a separate calculation but follows the same principle of using the right inputs to get the output you need.

Why Use Search Mode -1?

The default XLOOKUP returns the first match from top to bottom. That is fine when your data has unique entries, but when you have duplicate lookup values, you often want the most recent record. Examples include:

• Finding the latest price change for a product.
• Getting the most recent test score for a student.
• Retrieving the last status update for an order.
• Picking the latest entry from a log file.

In these cases, search_mode = -1 saves you from having to sort data or use array formulas like LOOKUP with a division trick.

How to Choose Between First and Last Match

Deciding which match you need depends on your data and the question you are answering:

• If your data is a list of unique IDs, the first and last match are the same, so it does not matter.
• If your data is chronological and you want the current value, use the last match (search_mode -1).
• If your data is a lookup table where each key appears once, the default is fine.
• If you need the earliest occurrence, use the default search mode (1).

Always check whether your data has duplicates. A quick way is to use conditional formatting to highlight duplicates, or use COUNTIF to see counts.

Common Problems and Mistakes

One common mistake is forgetting the search_mode argument entirely. If you omit it, you get the first match. Another mistake is using -1 in the match_mode argument by accident, which would mean approximate match, not last match. The two arguments are separate: match_mode controls how closely the value must match, and search_mode controls the direction of the search.

Another issue is that XLOOKUP does not work in older Excel versions (before Excel 365 or Excel 2021). If you are using an older version, you can use LOOKUP(2, 1/(range=value), return_range) as a workaround, but that is less intuitive.

Also, if your lookup array contains errors or blank cells, XLOOKUP may return unexpected results. Make sure your data is clean.

Comparing XLOOKUP with Other Functions

Before XLOOKUP, the common ways to find a last match were:

• VLOOKUP – returns the first match only, so not useful for last match without rearranging data.
• INDEX/MATCH – can find last match by using MATCH(lookup_value, lookup_array, 0) but only returns the first match. To get the last, you would need an array formula like INDEX(return_range, MAX(IF(lookup_range=lookup_value, ROW(lookup_range)-MIN(ROW(lookup_range))+1))).
• LOOKUP – the classic trick =LOOKUP(2, 1/(A2:A10=F2), C2:C10) works but is obscure and hard to read.

XLOOKUP with search_mode -1 is the cleanest and most straightforward solution. It is also faster than array formulas on large datasets.

Tips for Using XLOOKUP Last Match Effectively

Here are some practical tips:

• Use full column references (e.g., B:B) to make the formula dynamic, but be aware this can slow down calculations on very large sheets.
• If you need the last match for multiple lookup values, you can copy the formula down.
• Combine with FILTER or SORT if you need more complex logic.
• Use the if_not_found argument to show a friendly message instead of #N/A.
• Test with a small dataset first to confirm the direction is correct.

Frequently Asked Questions

Does XLOOKUP search from bottom by default?

No, the default search mode is 1, which searches from first to last. You must explicitly set the sixth argument to -1 to search from last to first.

Can I use XLOOKUP to find the last match in a row?

Yes, XLOOKUP works on rows as well as columns. If your data is in a row, the lookup_array and return_array can be horizontal ranges, and search_mode -1 will search from right to left.

What if there are blank cells in the lookup array?

Blank cells are treated as actual values (empty strings). If you are looking for a specific value, blanks will not match unless you look for an empty string. For last match, blanks do not affect the search unless your lookup value is blank.

Is XLOOKUP faster than INDEX/MATCH for last match?

Generally, XLOOKUP is more efficient than array formulas because it does not require array entry and is optimized for performance. On very large datasets, the difference can be noticeable.

Can I use wildcards with XLOOKUP for last match?

Yes, if you set match_mode to 2 (wildcard match), you can use * and ? in your lookup value. The search_mode -1 still works with wildcards, so you can find the last match that fits a pattern.

What if I need the second-to-last match?

XLOOKUP does not have a built-in way to get the second-to-last match. You would need to use a helper column or an array formula to filter and index. One approach is to use FILTER to get all matches and then index the one you want.

Related

• How to Host a Starbound Server: Step-by-Step
• How to Optimize Your WordPress RSS Feed
• Cloud Engineer Skills: What You Really Need

in t | Total time (min) | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2