How to look up the LAST matching value (not the first)
✓ Verified in LibreOffice 25.8.7.3When a key appears many times, return the most recent match — latest price, last status, newest entry.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =XLOOKUP(D2,A2:A6,B2:B6,,0,-1) | Search mode -1 = from the bottom up. Excel 2021+/365. Older: the LOOKUP trick below. |
| Google Sheets | =XLOOKUP(D2,A2:A6,B2:B6,,0,-1) | Identical; or =LOOKUP(2,1/(A2:A6=D2),B2:B6). |
| LibreOffice Calc | =LOOKUP(2,1/(A2:A6=D2),B2:B6) | XLOOKUP with -1 needs LibreOffice 24.8+; the LOOKUP trick works in any version. |
How it works
A normal VLOOKUP/XLOOKUP returns the FIRST match, but when rows are in date order you often want the LAST — the most recent price or status. Two ways: XLOOKUP with a search-mode of -1 scans from the bottom up and returns the last match directly. In any version, the classic LOOKUP trick does it too: (A2:A5=D2) is an array of TRUE/FALSE, 1 divided by it gives 1 for matches and errors for non-matches, and LOOKUP searching for 2 (bigger than any value) scans to the end and returns the value beside the LAST 1. For "apple" that's the second apple's price, 30. Sort your data chronologically first so 'last' means 'most recent'.
Verified, not just documented
We ran =LOOKUP(2,1/(A2:A5=D2),B2:B5) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 30 — exactly the expected result. Every formula here is confirmed by actually executing it.