Lookup & Reference
XLOOKUP: Return Blank ("") Instead of 0 on Empty Cells Without Erasing Real Zeros
Stop XLOOKUP from turning blank cells into 0—and avoid the ="" trap that accidentally wipes out genuine 0 balances—using an ISBLANK return-array check.
Why XLOOKUP(...) = "" breaks genuine numeric zeros
In desktop Excel, referencing an empty cell via XLOOKUP coerces the blank cell to numeric 0—turning ungraded exams or pending ship dates into 0 or 1900-01-00! Many guides suggest IF(XLOOKUP(...) = "", "", XLOOKUP(...)), or appending & "", which converts numbers into text strings or risks conflating blank cells with 0 in coerced comparisons.
Lookup over ISBLANK(return_range) solves both problems cleanly: XLOOKUP(key, keys, ISBLANK(values), FALSE) returns TRUE ONLY when the matched source cell is genuinely empty—returning "" for blank cells, preserving numeric 0 for zero-balance rows, and still returning "Not Found" for missing keys!
| Account ID | Source Balance Cell | Naive &"" Trick | ISBLANK-Guarded XLOOKUP |
|---|---|---|---|
| ACC-101 | 1250 | "1250" (breaks SUM!) | 1250 (number) |
| ACC-102 | Blank (pending audit) | "" | "" (clean blank string) |
| ACC-103 | 0 (paid in full) | "0" (text) | 0 (true numeric zero!) |
| ACC-999 | (Missing key) | "Not Found" | "Not Found" |
Zero-Safe Blank-Preserving XLOOKUP Formula
Checks ISBLANK(C2:C5) at the matched row first; returns "" only for genuinely empty source cells while preserving numeric 0 and "Not Found" fallbacks.
=IF(XLOOKUP(E2, A2:A5, ISBLANK(C2:C5), FALSE), "", XLOOKUP(E2, A2:A5, C2:C5, "Not Found"))INDEX + MATCH + ISBLANK Single-Match Alternative
Computes the row index once with LET and tests ISBLANK directly on the indexed cell reference.
=LET(idx, MATCH(E2, A2:A5, 0), IF(ISNA(idx), "Not Found", IF(ISBLANK(INDEX(C2:C5, idx)), "", INDEX(C2:C5, idx))))Try the example
Use the original values or change them to test your understanding.