Lookup & Reference
VLOOKUP If Blank Return Blank Without Wiping Out Legitimate Zero Balances
Test LEN(VLOOKUP(...)) = 0 instead of VLOOKUP(...) = 0 so empty source cells return a clean blank string "" while genuine numeric 0.00 balances remain intact.
Why IF(VLOOKUP(...) = 0, "", ...) corrupts zero-balance accounts and zero-defect counts
In classic desktop Excel, when VLOOKUP matches a row whose return cell is completely empty, Excel coerces that empty reference to numeric 0. Many spreadsheet users try to hide that unwanted 0 with IF(VLOOKUP(key, table, col, FALSE) = 0, "", VLOOKUP(key, table, col, FALSE))—which silently erases legitimate 0 balances (paid-in-full invoices, 0% discounts, or 0 safety incidents) because 0 = 0 is also TRUE!
Testing character length via LEN(VLOOKUP(key, table, col, FALSE)) = 0 separates the two states cleanly: an empty target cell has character length 0, whereas a genuine numeric 0 converts to "0" with LEN(0) = 1. Wrapping the expression in IFERROR handles missing lookup keys in the same formula.
| Source Return Cell | Naive = 0 Check | LEN(...) = 0 Guard | Audit Verdict |
|---|---|---|---|
| 1450 (Positive balance) | 1450 | 1450 | Both correct |
| Empty cell (Unbilled) | "" | "" | Blank preserved |
| 0 (Paid in full) | "" (WRONG — hides 0!) | 0 (Preserved) | Only LEN distinguishes 0 vs blank |
| Unmatched key | #N/A | "NOT_FOUND" (via IFERROR) | Clean missing-key label |
Zero-Safe VLOOKUP Blank Guard Formula
Bind or evaluate VLOOKUP(E2, $A$2:$C$5, 3, FALSE), return "" when LEN equals 0, and wrap in IFERROR for unmatched IDs.
=IFERROR(IF(LEN(VLOOKUP(E2, $A$2:$C$5, 3, FALSE)) = 0, "", VLOOKUP(E2, $A$2:$C$5, 3, FALSE)), "NOT_FOUND")Modern LET Variant Avoiding Double VLOOKUP Evaluation
In Excel 365 and Arte Sheets, bind hit = VLOOKUP(...) once inside LET so large lookup tables are scanned only once per row.
=IFERROR(LET(hit, VLOOKUP(E2, $A$2:$C$5, 3, FALSE), IF(LEN(hit) = 0, "", hit)), "NOT_FOUND")Try the example
Use the original values or change them to test your understanding.