Lookup & Reference
Find the Last Populated Row Number in Numeric, Text, and Mixed Ranges
Calculate the exact worksheet row number, relative table index, and bottom-most populated value across contiguous no-blank tables vs gapped mixed text/number logs.
Why ROW(first) + COUNTA(range) - 1 only works when no internal blanks exist
When a table has zero internal blank cells, adding COUNTA(B2:B8) - 1 to the starting row ROW(B2) immediately yields the bottom populated worksheet row regardless of whether cells hold numbers, text, or dates. However, the moment a single intermediate row is left blank (for example, B4 = ""), COUNTA undercounts by 1 for every internal gap and points above the true last entry.
Tracking populated row coordinates explicitly via =IF(ISBLANK(B2), 0, ROW(B2)) (or separate numeric/text trackers ISNUMBER(B2) * ROW(B2) and ISTEXT(B2) * ROW(B2)) lets MAX locate the exact last worksheet row across numeric-only, text-only, and gapped mixed ranges alike.
| Data Structure | Formula Pattern | Internal Blank Safe? |
|---|---|---|
| Contiguous Mixed (No Blanks) | ROW(B2) + COUNTA(B2:B8) - 1 | No (shifts up per gap) |
| Gapped Mixed (Text + Numbers) | MAX of IF(ISBLANK(B2), 0, ROW(B2)) | Yes — exact worksheet row |
| Last Numeric Row Only | MAX of IF(ISNUMBER(B2), ROW(B2), 0) | Yes — ignores trailing text notes |
| Last Text Row Only | MAX of IF(ISTEXT(B2), ROW(B2), 0) | Yes — ignores trailing numeric totals |
Gap-Safe Last Worksheet Row & Relative Offset Formula
Record ROW(B2) on populated rows and take MAX(C2:C8); subtract ROW(B2) - 1 when you need the 1-based position inside INDEX(B2:B8, pos).
=INDEX(B2:B8, MAX(C2:C8) - ROW(B2) + 1)Contiguous No-Blank Fast Formula
On strict database exports guaranteed to have no blank keys, ROW(B2) + COUNTA(B2:B8) - 1 computes the last row with zero helper columns.
=ROW(B2) + COUNTA(B2:B8) - 1Try the example
Use the original values or change them to test your understanding.