Find the Last Match in Excel With XLOOKUP

XLOOKUP finds the first match by default, but setting its last argument, search_mode, to -1 makes it search from the bottom up and return the last match instead. That's how you get the latest price, the most recent status or the last test score for a person from a log that grows downwards. This guide covers the formula, the argument most people mix up, and options for unsorted data and older Excel.

Why lookups return the first match#

VLOOKUP, MATCH and XLOOKUP all search from the top by default and stop at the first match. That's a problem for logs, where the newest row is at the bottom. Here is a supplier cost list, added to each week:

With Paper in F2, =XLOOKUP(F2,A2:A8,C2:C8) returns 4.20, the week 1 cost. You want 4.50, the latest one, from week 5. A plain VLOOKUP has the same problem and no way to change direction.

How to find the last match with XLOOKUP#

Add the 4th, 5th and 6th arguments, ending with -1:

=XLOOKUP(F2,A2:A8,C2:C8,"Not found",0,-1)

The result is 4.5. Here is what each argument does:

XLOOKUP starts at row 8, moves up, and stops at the first "Paper" it meets: row 7. You can leave the 4th and 5th arguments empty with commas, =XLOOKUP(F2,A2:A8,C2:C8,,,-1), but writing them out makes the formula easier to check. XLOOKUP needs Excel 2021, Microsoft 365 or Excel for the web; the XLOOKUP guide and the XLOOKUP function reference cover the basics.

The mistake to avoid: match_mode vs search_mode#

The most common error is putting -1 in the 5th argument instead of the 6th.

=XLOOKUP(F2,A2:A8,C2:C8,,-1)

That -1 is match_mode, which means "exact match, or the next smaller value". For text that exists, it still returns the first match, 4.20, so the formula looks fine but answers the wrong question. Count the commas: search_mode is the sixth argument.

Match modes 2 and wildcards are covered in XLOOKUP wildcard match. You can combine them with search_mode: =XLOOKUP("P*",A2:A8,A2:A8,,2,-1) returns the last product starting with P, which is Pens.

How to find the position of the last match#

To get the row number instead of the value, use XMATCH with the same -1:

=XMATCH(F2,A2:A8,0,-1)

The result is 6: the last Paper is the 6th row of A2:A8. Use it with INDEX to return from any column: =INDEX(C2:C8,XMATCH(F2,A2:A8,0,-1)) also returns 4.5. MATCH has no search direction, so it can only give you the first position; see the MATCH function for what it can do.

How to find the latest entry when the list isn't sorted#

search_mode -1 returns the last row, which is the latest only if the list is in date order. If rows can arrive out of order, find the latest date first, then look up the row with both the product and that date.

Step 1: Find the latest week for the product#

=MAXIFS(B2:B8,A2:A8,F2)

This returns 5, the highest week number for Paper. MAXIFS needs Excel 2019 or later.

Step 2: Look up the row with both criteria#

=XLOOKUP(1,(A2:A8=F2)*(B2:B8=MAXIFS(B2:B8,A2:A8,F2)),C2:C8)

This returns 4.5, wherever the week 5 row sits in the list. Multiplying the two tests is the standard way to look up on two conditions; see lookup with multiple criteria for how it works.

How to get the last non-empty value in a column#

The same idea finds the last filled cell, such as the latest reading in a column that grows each day:

=XLOOKUP(TRUE,C2:C100<>"",C2:C100,,0,-1)

C2:C100<>"" is TRUE for every filled cell. Searching from the bottom, XLOOKUP returns the value of the last one: here, 5.9. The range can extend past your data, so new rows are picked up automatically.

How to find the last match in older Excel#

Without XLOOKUP, the classic formula uses LOOKUP:

=LOOKUP(2,1/(A2:A8=F2),C2:C8)

1/(A2:A8=F2) gives 1 for matching rows and #DIV/0! for the rest. LOOKUP searches for 2, can't find it, ignores the errors, and settles on the last 1 in the list. It returns the matching value from C2:C8: 4.5. It works in all versions without Ctrl+Shift+Enter, but it's hard to read, so add a note next to it.

Two other choices: sort the data newest first (Data > Sort, Largest to Smallest on the date column) so a normal VLOOKUP finds the latest row, or use a pivot table with the MAX of the date.

Common mistakes when finding the last match#

• -1 in the wrong slot. As above, the 5th argument is match_mode; search_mode is the 6th.
• Assuming "last" means "latest". search_mode -1 returns the bottom-most row. If the data isn't in date order, use the MAXIFS method.
• Ranges of different sizes, such as A2:A8 and C2:C9, which return #VALUE!.
• Extra spaces or text-numbers in the lookup column, which make XLOOKUP skip the row you expect. Clean the data with TRIM, or convert text to numbers.
• Forgetting if_not_found. Without it, a product with no rows shows #N/A, which can break totals that use the result.

Practise it#

Try the live exercise on this page to return the latest Paper cost. Then practise XLOOKUP wildcards and match modes in a live spreadsheet, where you'll use XLOOKUP's optional arguments, including the match modes this guide warns you not to confuse with search_mode.