Lookup & Reference
Find the First Non-Blank Value and Row Number in a Column (XLOOKUP + XMATCH)
Locate the earliest populated entry, its relative position, its worksheet row number, and the last non-blank entry in sparse columns—including legitimate 0 values.
Why ISBLANK(B2:B8) beats > 0 and <> "" tests when legitimate 0s exist
Testing B2:B8 > 0 skips negative adjustments, legitimate zero opening balances, and text entries—and in loose scalar/array comparisons, empty cells can coerce to 0 alongside numeric zeros. Evaluating ISBLANK(B2:B8) returns TRUE strictly for genuinely unpopulated cells and FALSE for every entered number (including 0) or text token.
Searching for FALSE inside XLOOKUP(FALSE, ISBLANK(B2:B8), B2:B8, "ALL_BLANK") retrieves the first non-blank value (or the last non-blank value when search_mode = -1), while ROW(B2) + XMATCH(FALSE, ISBLANK(B2:B8)) - 1 pinpoints its exact worksheet row.
| Target Lookup | Formula Pattern | All-Blank Fallback |
|---|---|---|
| First non-blank value | XLOOKUP(FALSE, ISBLANK(B2:B8), B2:B8, "ALL_BLANK") | "ALL_BLANK" |
| Last non-blank value | XLOOKUP(FALSE, ISBLANK(B2:B8), B2:B8, "ALL_BLANK", 0, -1) | "ALL_BLANK" |
| First populated sheet row | IFERROR(ROW(B2) + XMATCH(FALSE, ISBLANK(B2:B8)) - 1, 0) | 0 |
First & Last Non-Blank Value and Row Formula
Combine XLOOKUP(FALSE, ISBLANK(...)) for value extraction with XMATCH(FALSE, ISBLANK(...)) + ROW(anchor) - 1 for worksheet row indexing.
=XLOOKUP(FALSE, ISBLANK(B2:B8), B2:B8, "ALL_BLANK")Try the example
Use the original values or change them to test your understanding.