How to stop a lookup returning 0 for blank cells
Formula (Excel and Google Sheets)
=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))Check the result before showing it: =LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price)).
- Result on the example
- one result per row in
F2, filled down toF6 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F6. Change any input and the result updates, or edit the formula to experiment.
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.
=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))Result399
price399A name defined in LET, or a named range.
E2"P-104"The value in E2.
$A$2:$A$76 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 6 cells in one column.
$C$2:$C$76 rows × 1 col: $34.99, (empty), (empty), $399.00, …A range: 6 cells in one column.
XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7)399XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E2"P-104" - lookup_array
$A$2:$A$76 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
$C$2:$C$76 rows × 1 col: $34.99, (empty), (empty), $399.00, …
- lookup_value
price=""FALSEThe = operator tests whether the two sides are equal.
IF(price="","",price)399IF: Returns one value if a test is true, another if false.- logical_test
price=""FALSE - value_if_true
""not used - value_if_false
price399
- logical_test
LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))399LET: Names values inside a formula.- name1
price399 - value1
XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7)399 - name2, value2
IF(price="","",price)399
- name1
In Google Sheets
The same formula works in Google Sheets: =LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price)). 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$7,3,FALSE)="","",VLOOKUP(E2,$A$2:$C$7,3,FALSE)) | $399.00, , $49.00… | Every version: the lookup is simply written twice. |
| =XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7)&"" | 399, , 49…Different result | 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) | $399.00, $0.00, $49.00…Different result | 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.
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.
Stop a lookup returning 0 for blank cells
F2, return the sale price for the SKU in E2, or leave the cell blank when there is none (not 0). Fill down to F6.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).
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.