Excel Last Non-Empty Cell in a Column
By Szabó Gergő · Updated
Search from the bottom for the last nonblank cell so dashboards always show the newest entry.
Syntax and arguments
=XLOOKUP(TRUE,A2:A1000<>"",A2:A1000,,,-1)- range
- The column to scan, tall enough for new rows.
Last non-empty cell examples
Log in A2:A1000.
=XLOOKUP(TRUE,A2:A1000<>"",A2:A1000,,,-1)Search mode -1 starts at the bottom and returns the last nonblank.
Ignore labels.
=LOOKUP(2,1/(ISNUMBER(A2:A1000)),A2:A1000)LOOKUP 2 walks an array of 1 and #DIV/0! and keeps the last 1.
Need the row, not the value.
=MATCH(2,1/(A2:A1000<>""))Returns the position inside A2:A1000. Add 1 if your range starts at A2 and you need a worksheet row.
Common mistakes
Scanning the whole column A:A on a huge file
Limit the range to a realistic max row for speed.
Counting spaces as values
TRIM or test LEN(TRIM(A2:A1000))>0.
Using COUNTA when there are gaps
COUNTA is a count, not the last cell after blanks in the middle.
Last non-empty cell FAQ
Does this work on a row?
Yes. Point the arrays at 2:2 or A1:Z1.
Can I return a matching column?
=XLOOKUP(TRUE,A2:A1000<>"",C2:C1000,,,-1) returns the last used row from column C.
What about tables?
In a table, [@] is the current row. For the last table row use INDEX([Amount],ROWS([Amount])).