Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to get the value of the last non-empty cell in Excel and Google Sheets
Excel & Google Sheets
=LOOKUP(2,1/(A2:A6<>""),A2:A6)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the column that contains your data list, e.g. column A starting at row 2.
- 2Enter =LOOKUP(2,1/(A2:A6<>""),A2:A6) in an empty cell — the LOOKUP trick uses the fact that dividing 1 by a TRUE/FALSE array produces 1s and errors, and LOOKUP finds the last 1.
- 3Press Enter; the formula returns the value of the last non-empty cell in the range.
- 4Extend the range (e.g. A2:A1000) to accommodate future data without changing the formula.
Tips
- Wrap the formula in IFERROR to return a friendly message when the entire range is empty: =IFERROR(LOOKUP(2,1/(A2:A100<>""),A2:A100),"No data").
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Does this work for numeric and text values?
Yes — the <> "" test treats any non-blank cell (numbers, text, dates) as non-empty.
Why use LOOKUP(2,...) instead of LOOKUP(1,...)?
The lookup value 2 is larger than the maximum value in the array (1), so LOOKUP always lands on the last 1 — i.e. the last non-empty row.