Lookup & Reference
XLOOKUP returns 0 for blank cells: return blank ("") without breaking real zeros
Stop XLOOKUP and VLOOKUP from turning empty target cells into 0 (or 1900-01-00 dates) while keeping genuine 0 balances and numeric data types intact.
Why & "" corrupts numbers and IF(XLOOKUP=0, "") deletes real zeros!
In desktop Excel, when XLOOKUP or VLOOKUP finds a matching row whose return cell is completely empty, Excel coerces the empty reference to numeric 0 (which formats as "1/0/1900" in date columns!).
Two popular workarounds introduce silent bugs: appending & "" turns every real number (450) into text ("450"), while IF(XLOOKUP(...)=0, "", ...) wipes out genuine $0.00 account balances! Locating the row index with XMATCH and testing COUNTBLANK(INDEX(ReturnCol, i)) = 1 distinguishes blank cells from real 0s while keeping numbers numeric.
| Account (A) | Balance (B) | XLOOKUP(...) & "" | XMATCH + COUNTBLANK Safe Output |
|---|---|---|---|
| ACC-1 (Zero bal) | 0 | "0" (Text!) | 0 (Numeric zero kept!) |
| ACC-2 (Funded) | 450 | "450" (Text!) | 450 (Numeric kept!) |
| ACC-3 (Unset blank) | (blank) | "0" in Excel when the raw blank lookup becomes 0 | "" (Clean blank!) |
| ACC-9 (Missing ID) | — | #N/A | "Not Found" |
Type-safe, real-zero-safe blank lookup formula
Preserves numeric 0, numeric 450, empty string "", and custom missing-key text across both Excel and Arte Sheets.
=LET(i, XMATCH("ACC-3", A2:A4), IF(ISNA(i), "Not Found", IF(COUNTBLANK(INDEX(B2:B4, i))=1, "", INDEX(B2:B4, i))))Verify real numeric zero is preserved
On ACC-1 where Balance = 0, COUNTBLANK is 0, so numeric 0 is returned untouched.
=LET(i, XMATCH("ACC-1", A2:A4), IF(ISNA(i), "Not Found", IF(COUNTBLANK(INDEX(B2:B4, i))=1, "", INDEX(B2:B4, i))))Inputs and interpretation
COUNTBLANK also treats a formula returning an empty string as blank; whitespace is text. Duplicate keys use the first match. The empty-string result is not a physically empty cell. Date formatting of zero varies by locale and format; Arte Sheets raw XLOOKUP can return an empty string where Excel returns zero.
Try the example
Use the original values or change them to test your understanding.