# How to stop a lookup returning 0 for blank cells

Check the result before showing it: `=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))`.

**Excel:** `=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))` (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 |
| --- | --- | --- | --- | --- | --- |
| SKU | Product | Sale price |  | Order SKU | Sale price |
| P-101 | Desk lamp | 34.99 |  | P-104 |  |
| P-102 | Office chair |  |  | P-102 |  |
| P-103 | Monitor arm |  |  | P-106 |  |
| P-104 | Standing desk | 399 |  | P-103 |  |
| P-105 | Keyboard |  |  | P-101 |  |
| P-106 | Webcam | 49 |  |  |  |

Formula in F2, filled to F6: `=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))`

Result: $399.00; ; $49.00; ; $34.99

## How it works

When the matching cell is empty, Excel lookups return 0, which reads like a real price of $0.00. LET stores the lookup result as `price`, and IF shows "" when it is empty and the price otherwise, so the lookup runs only once and real numbers stay numbers.

## Other ways

- `=IF(VLOOKUP(E2,$A$2:$C$7,3,FALSE)="","",VLOOKUP(E2,$A$2:$C$7,3,FALSE))`: Every version: the lookup is simply written twice.
- `=XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7)&""`: Short, but it turns every result into text. (Prices come back as text, so they lose their currency format and don't add up.)
- `=XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7)`: Shown for contrast: the plain lookup. Google Sheets shows these as blank, Excel as 0. (Shows $0.00 for products with no sale price.)

## Common mistakes

- Formatting zeros away (a number format like `0;-0;;@`) hides them but SUM and AVERAGE still count them as 0.
- Don't confuse this with #N/A. A blank result means the SKU was found but the cell is empty; a missing SKU still needs IFNA or XLOOKUP's 4th argument.

## Questions

**Why does Google Sheets show a blank but Excel shows 0?**

Google Sheets passes empty cells through a lookup as empty; Excel converts them to 0.

**How do I show "No sale" instead of a blank?**

Change the first result of IF: `IF(price="","No sale",price)`.

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/lookup-returns-0-instead-of-blank