How to find the last value in a column
✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)Get the most recent entry in a column that keeps growing — the last non-blank cell.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =LOOKUP(2,1/(A2:A100<>""),A2:A100) | Excel 365 alternative: =TAKE(FILTER(A2:A100,A2:A100<>""),-1). |
| Google Sheets | =ARRAYFORMULA(LOOKUP(2,1/(A2:A100<>""),A2:A100)) | Executed 2026-08-30: the unwrapped =LOOKUP(2,1/(A2:A100<>""),A2:A100) returned #N/A in Google Sheets against cherry in LibreOffice. Google Sheets does no implicit array evaluation inside a scalar function’s arguments, so 1/(A2:A100<>"") reduces to a single value before LOOKUP sees it; ARRAYFORMULA around the whole formula is the documented fix, and =INDEX(A2:A100,COUNTA(A2:A100)) is simpler for a gap-free column. Both are documented Sheets syntax, carrying no executed result of their own. |
| LibreOffice Calc | =LOOKUP(2,1/(A2:A100<>""),A2:A100) | Identical. |
How it works
The classic LOOKUP trick: (A2:A100<>"") is an array of TRUE/FALSE, and 1 divided by it gives 1 for filled cells and a #DIV/0! error for blanks. LOOKUP searches for 2 — larger than anything in the array — so it scans to the end and returns the value beside the last 1, i.e. the last non-blank cell. It skips gaps: in the sample, A4 is empty but the result is still "cherry" from A5.
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) |
|---|---|---|---|
=ARRAYFORMULA(LOOKUP(2,1/(A2:A100<>""),A2:A100)) | ARRAYFORMULA wrap, so 1/(A2:A100<>"") stays an array on its way into LOOKUP | n/a (Sheets-only formula) | cherry Google Sheets alternative (executed 2026-08-30) |
Verified, not just documented
We ran =LOOKUP(2,1/(A2:A100<>""),A2:A100) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned cherry — 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 (cherry) — 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
LOOKUP · ARRAYFORMULA — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to average the top N scores (drop the lowest)
- How to look up the LAST matching value (not the first)
- How to look up a value with two criteria (multi-condition lookup)
- How to reverse a text string
- How to sum with multiple criteria (SUMIFS)