Lookup & Reference
Get the last match in a column (reverse bottom-to-top XLOOKUP)
Pass -1 as XLOOKUP's 6th search_mode argument to pull the most recent appended price, status, or log row for a repeated key.
Why VLOOKUP and MATCH(..., 0) always stop at the first row
When chronological purchase logs append new receipts at the bottom, SKU-10 appears multiple times—at $18.50, $21.00, and most recently $23.75. Default top-to-bottom lookups stop at row 2 and return stale cost $18.50.
Setting XLOOKUP's 5th argument (match_mode) to 0 (exact match) and 6th argument (search_mode) to -1 scans from the last row upward and returns $23.75 immediately.
Last means the last physical matching row. It represents the latest receipt only when the source is maintained in chronological order; sorting changes that interpretation.
| Row | Sku (A) | UnitCost (B) | Search Direction |
|---|---|---|---|
| 2 | SKU-10 | 18.5 | First match (search_mode = 1) |
| 3 | SKU-20 | 42 | |
| 4 | SKU-10 | 21 | |
| 5 | SKU-10 | 23.75 | Last match (search_mode = -1) |
Reverse XLOOKUP formula
Include the 4th if_not_found argument ("Not found") so unknown SKUs return a clear message.
=XLOOKUP(C2, A2:A5, B2:B5, "Not found", 0, -1)Compare first match vs last match
Changing search_mode from -1 to 1 retrieves the initial historical cost (18.5).
=XLOOKUP(C2, A2:A5, B2:B5, "Not found", 0, 1)Try the example
Use the original values or change them to test your understanding.