How to Find the Last Match with XLOOKUP in Excel

Search from the bottom of a log to get the latest price, compare it with the first, and find the last date a product was bought.

You will learn to

• Return the most recent match with search_mode -1
• Compare the last and first match for the same item
• Combine a wildcard with a bottom-up search

How it works

A normal lookup returns the first match, which in a chronological log is the oldest entry. XLOOKUP’s sixth argument, search_mode, changes the direction: -1 searches from the last row up, so =XLOOKUP("Arabica beans 1kg",B2:B11,C2:C11,,0,-1) returns the most recent price paid.

Subtract the first match from the last to see how a price has moved, and combine search_mode -1 with wildcard matching (match_mode 2) to find the latest entry whose name merely contains a word, such as "milk". The log does not need to be sorted, only kept in date order.

=XLOOKUP(F1,B2:B11,C2:C11,,0,-1)