Lookup & Reference
Get the last non-blank value and row position in a column with blanks
Retrieve the bottom-most populated entry and its row index—even when empty spacer rows appear throughout the column—using reverse XLOOKUP and XMATCH.
Why COUNTA fails to find the last row whenever blank gaps exist!
COUNTA counts entries rather than locating their positions. In this example with gaps, INDEX(range,COUNTA(range)) returns an earlier entry. A count is therefore not a general last-row locator.
Use B2 = IF(COUNTBLANK(A2)>0,0,1) and fill through B7. This treats blank cells and empty formula results as absent, but retains numeric zero. Reverse XLOOKUP and XMATCH search these flags from the bottom. Last means row order, not latest timestamp; spaces count as present.
| Relative Pos | Status Log (A2:A7) | Nonblank helper flag (0 or 1) | Bottom-Up Search (-1) |
|---|---|---|---|
| Pos 1 (Row 2) | "Draft v1" | 1 | Skipped (earlier) |
| Pos 2 & 4 | "" (Blank gaps) | 0 | Ignored |
| Pos 3 (Row 4) | "Review v2" | 1 | COUNTA=3 stops here by mistake! |
| Pos 5 (Row 6) | "Signed Final" | 1 | Matched! True last entry (Pos 5) |
| Pos 6 (Row 7) | "" (Trailing blank) | 0 | Ignored |
Return the last non-blank value in a mixed text/number column
Searches B2:B7 from bottom to top (-1) for the last 1 and returns the corresponding entry from A2:A7.
=XLOOKUP(1, B2:B7, A2:A7, "All Blank", 0, -1)Return the exact relative row position (or sheet row) of the last entry
XMATCH(..., 0, -1) gives the 1-based offset (5); adding ROW(A2) - 1 gives the absolute worksheet row (6).
=IFERROR(XMATCH(1,B2:B7,0,-1),"All Blank")Try the example
Use the original values or change them to test your understanding.