How to look up the LAST matching value (not the first)

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

When a key appears many times, return the most recent match — latest price, last status, newest entry.

The formula

App	Formula	Notes
Excel (desktop)	`=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)`	Native XLOOKUP with search mode -1 is the documented Sheets route. Skip the bare LOOKUP trick here: executed 2026-08-30, =LOOKUP(2,1/(A2:A5=D2),B2:B5) returned #N/A in Google Sheets against 30 in LibreOffice, because Google Sheets does no implicit array evaluation inside a scalar function’s arguments. =ARRAYFORMULA(LOOKUP(2,1/(A2:A5=D2),B2:B5)) is the documented wrap. Both are documented Sheets syntax, carrying no executed result of their own.
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'.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=XLOOKUP(D2,A2:A6,B2:B6,,0,-1)`	Native XLOOKUP with search mode -1 (scan from the last row up)	n/a (Sheets-only formula)	30 Google Sheets alternative (executed 2026-08-30) NOTE: written with the PLAIN function name; the LibreOffice reference run executed the _xlfn. storage form of this formula, so the two runs are not byte-identical inputs

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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned #N/A, which is not what LibreOffice returned (30) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

XLOOKUP · LOOKUP — see full Excel, Google Sheets & LibreOffice compatibility for each.