Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to return the last non-blank value in Excel and Google Sheets
Excel & Google Sheets
=LOOKUP(2,1/(B2:F2<>""),B2:F2)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
- 1Enter =LOOKUP(2,1/(B2:F2<>""),B2:F2) in a result cell — the formula creates a 1/TRUE = 1 array and searches for 2, which is never found, so LOOKUP settles on the last 1 (last non-blank).
- 2Adjust the range B2:F2 to match your actual row or column span.
- 3For a vertical range (column), change both occurrences to a column reference like B2:B20.
Tips
- This LOOKUP trick works in both Excel and Sheets and doesn't require Ctrl+Shift+Enter — it's a regular formula that handles arrays internally.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does LOOKUP(2,...) work to find the last value?
LOOKUP requires the lookup array to be sorted ascending. When all values are 1 and none match 2, LOOKUP returns the last element — effectively the last non-blank.
Does this work if the last cell contains a number?
Yes — it returns the last non-blank value regardless of data type.