Need the most recent entry — the last price, the latest status? XLOOKUP’s search mode can scan from the bottom up, returning the last match instead of the first.
-1 searches last-to-first, so you get the most recent matching record.
The example
The latest price for a repeated product.
| A | B | |
|---|---|---|
| 1 | Product (log) | Price |
| 2 | Widget | 10 |
| 3 | Widget | 12 (latest) |
The formula
Search from the bottom:
How it works
Search mode sets the scan direction:
- The 6th argument is search mode:
1first-to-last (default),-1last-to-first. - With
-1, XLOOKUP returns the last row that matches — the most recent in a chronological log. - The 4th and 5th arguments (if_not_found, match mode) still go in their slots; use commas to skip them.
- For sorted data, search modes 2 and -2 enable fast binary search.
Pre-XLOOKUP last match needed an array LOOKUP trick: =LOOKUP(2, 1/(products=A2), prices) returns the last match in any version. XLOOKUP’s search mode -1 is the readable modern replacement.
Try it: interactive demo
Latest price for the product.
Variations
First match (default)
Omit search mode:
Legacy last match
Any version:
Binary search
Sorted data, fast:
Pitfalls & errors
Count the commas. Search mode is the 6th argument; skip if_not_found and match mode with commas: XLOOKUP(v, l, r, , 0, -1).
“Last” assumes order. Last-to-first returns the last row, which is only “most recent” if the data is in chronological order.
365/2021 only. Use the LOOKUP(2,1/(...)) trick in older Excel.
Practice workbook
Frequently asked questions
How do I find the last match with XLOOKUP?
How do I get the last match in older Excel?
Where does the search mode go in XLOOKUP?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class