Last number
=LOOKUP(2,1/ISNUMBER(A2:A),A2:A)Ignores text and blanks.
PASTE-READY RECIPE
Return the Last Non-Empty Value in Google Sheets
Find the most recent filled value in a column while ignoring blanks.
QUICK ANSWER
The formula and where it fits.
Find the most recent filled value in a column while ignoring blanks. Use =LOOKUP(2,1/(A2:A<>""),A2:A), then replace the sample ranges with the columns in your sheet.
LOOKUP searches for 2 but only sees 1 values where the column is filled, so it returns the last one.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | |
|---|---|
| 1 | Status history |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 |
FORMULA OUTPUTApprovedRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Last date
=MAX(FILTER(A2:A,A2:A<>""))Returns the latest date.
XLOOKUP version
=XLOOKUP(TRUE,A2:A<>"",A2:A,"",0,-1)Searches from the bottom upward.
COMMON ERRORS
When the result looks wrong.
The column contains formula blanks
Use A2:A<>"" rather than ISBLANK.
The result includes a header
Start the range below the header row.
QUESTIONS
Answers before you paste.
The column contains formula blanks
Use A2:A<>"" rather than ISBLANK.
The result includes a header
Start the range below the header row.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.