# Why does VLOOKUP return 0 instead of a blank?

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)).

**Excel:** `=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer | Email | Phone |  | Customer | Phone |
| Contoso | buy@contoso.example | 555-0101 |  | Litware |  |
| Fabrikam | ap@fabrikam.example |  |  | Contoso |  |
| Litware | hello@litware.example | 555-0148 |  | Fabrikam |  |
| Northwind | orders@northwind.example |  |  | Adatum |  |
| Adatum | info@adatum.example | 555-0177 |  |  |  |

Formula in F2, filled to F5: `=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))`

Result: 555-0148; 555-0101; ; 555-0177

## 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.

## Other ways

- `=IF(VLOOKUP(E2,$A$2:$C$6,3,FALSE)="","",VLOOKUP(E2,$A$2:$C$6,3,FALSE))`: The same test without LET, for older versions. The lookup runs twice.
- `=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6)&""`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/vlookup-returns-0-instead-of-blank