Lookup & Reference
Get the last number or nonempty text in a column
Scan upward from the bottom of a partially filled column with XLOOKUP(..., 0, -1) to pull the latest balance—even when gaps exist.
Why INDEX(B2:B13, COUNTA(B2:B13)) fails when intermediate rows are blank
A 12-month cash tracker has trailing blank rows for future months—and if March was skipped during a system migration, COUNTA counts 3 filled cells and INDEX(..., 3) lands on blank March instead of April!
Scanning bottom-up (search_mode = -1) with XLOOKUP(TRUE, (ISNUMBER(B2:B6) + (ISTEXT(B2:B6)*(B2:B6<>""))) > 0, B2:B6, "No data", 0, -1) finds the physically last populated cell ($51,200 in April) regardless of earlier gaps.
| Row | Month (A) | ClosingCash (B) | Status |
|---|---|---|---|
| 2 | Jan | 42000 | Populated |
| 3 | Feb | 45500 | Populated |
| 4 | Mar | (blank) | Gap in middle! |
| 5 | Apr | 51200 | Last non-empty cell |
| 6 | May | (blank) | Future month |
Latest value and latest period label formulas
Swapping return_array from B2:B6 to A2:A6 retrieves the month label ("Apr") of that latest closing figure.
=XLOOKUP(TRUE, (ISNUMBER(B2:B6) + (ISTEXT(B2:B6) * (B2:B6<>""))) > 0, B2:B6, "No data", 0, -1)Get the month label of the last populated balance
Returning A2:A6 identifies "Apr" as the most recent closed period.
=XLOOKUP(TRUE, (ISNUMBER(B2:B6) + (ISTEXT(B2:B6) * (B2:B6<>""))) > 0, A2:A6, "No data", 0, -1)Inputs and interpretation
This predicate includes numbers (including zero) and nonempty text, and excludes empty strings and boolean cells. Spaces count as text; error cells need cleaning before evaluation. Last means physical row order, not the maximum date: sort deliberately before calling it the latest record.
Try the example
Use the original values or change them to test your understanding.