How to find the last value in a column
✓ Verified in LibreOffice 25.8.7.3Get the most recent entry in a column that keeps growing — the last non-blank cell.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =LOOKUP(2,1/(A2:A100<>""),A2:A100) | Excel 365 alternative: =TAKE(FILTER(A2:A100,A2:A100<>""),-1). |
| Google Sheets | =LOOKUP(2,1/(A2:A100<>""),A2:A100) | Identical; Sheets also has =INDEX(A2:A100,COUNTA(A2:A100)) for gap-free columns. |
| 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.
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. Every formula here is confirmed by actually executing it.