Why does VLOOKUP return 0 instead of a blank?
Formula (Excel and Google Sheets)
=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))A formula that reads an empty cell shows 0. Test the result and return "": =LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p)).
- Result on the example
- one result per row in
F2, filled down toF5 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F5. Change any input and the result updates, or edit the formula to experiment.
How it works
Fabrikam has no phone number, so the lookup lands on an empty cell, and in Excel a formula that reads an empty cell shows 0. LET keeps the lookup result as p; IF returns "" when p is empty and the number otherwise. Google Sheets already shows a blank there, and the same formula works in it.
=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))Result"555-0148"
p"555-0148"A name defined in LET, or a named range.
E2"Litware"The value in E2.
$A$2:$A$65 rows × 1 col: "Contoso", "Fabrikam", "Litware", "Northwind", …A range: 5 cells in one column.
$C$2:$C$65 rows × 1 col: "555-0101", (empty), "555-0148", (empty), …A range: 5 cells in one column.
XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6)"555-0148"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E2"Litware" - lookup_array
$A$2:$A$65 rows × 1 col: "Contoso", "Fabrikam", "Litware", "Northwind", … - return_array
$C$2:$C$65 rows × 1 col: "555-0101", (empty), "555-0148", (empty), …
- lookup_value
p=""FALSEThe = operator tests whether the two sides are equal.
IF(p="","",p)"555-0148"IF: Returns one value if a test is true, another if false.- logical_test
p=""FALSE - value_if_true
""not used - value_if_false
p"555-0148"
- logical_test
LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))"555-0148"LET: Names values inside a formula.- name1
p"555-0148" - value1
XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6)"555-0148" - name2, value2
IF(p="","",p)"555-0148"
- name1
In Google Sheets
The same formula works in Google Sheets: =LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(VLOOKUP(E2,$A$2:$C$6,3,FALSE)="","",VLOOKUP(E2,$A$2:$C$6,3,FALSE)) | 555-0148, 555-0101, … | The same test without LET, for older versions. The lookup runs twice. |
| =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6)&"" | 555-0148, 555-0101, … | Joining "" turns the result into text, so an empty cell comes back empty. Numbers come back as text too, so use it only on text columns. |
Common mistakes
- Hiding zeros with a number format also hides real zeros, like a stock count of 0.
&""turns numbers into text, which then won't add up.- A customer missing from the table still returns #N/A. Add XLOOKUP's fourth argument, or IFNA, for that case.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does VLOOKUP return 0 instead of a blank
F2, look up the phone number for the customer in E2, showing a blank instead of 0 when the phone cell is empty. Fill down to F5.Questions
Why does Excel show 0 at all?
A formula can't return "nothing". When it reads an empty cell, Excel turns it into 0.
Does Google Sheets show 0 too?
No. Google Sheets returns an empty result for an empty cell, so the plain lookup already looks blank.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.