VLOOKUP returns 0 instead of blank: how to fix it

Why an empty cell comes back as 0

The sheet is an order tracker. Northside Gym's order SO-5103 has not shipped or been invoiced, so its Shipped, Tracking no. and Balance due cells are empty. Look up its balance and you get 0, which reads as "paid in full", exactly like Oak & Ivy's real 0 in SO-5105.

This is not a VLOOKUP bug. Any formula that returns the contents of an empty cell shows 0: =E4, INDEX/MATCH and XLOOKUP do the same. It happens in text columns too: the tracking number for SO-5103 comes back as 0, not as blank. IFERROR does not help, because nothing went wrong: =IFERROR(VLOOKUP(G2,A2:E8,5,FALSE),"") still shows 0.

Return a blank with IF, or LET to look up once

Compare the result with "". An empty cell counts as equal to "", a real 0 does not, so =IF(VLOOKUP(G2,A2:E8,5,FALSE)="","",VLOOKUP(G2,A2:E8,5,FALSE)) shows a blank for SO-5103 and still shows 0 for SO-5105. That makes it better than any trick that hides all zeros.

The IF version runs the same lookup twice, which is long to read and slower on big sheets. In Excel 365 and 2021, LET stores the result under a name and reuses it. In older versions keep the IF version.

Know what the blank is: "" is empty text, not an empty cell. SUM down a column of these results skips it, so a total of a blank and 920 is 920. But arithmetic with + fails: adding 10 to the SO-5103 result gives #VALUE!. If the result feeds a calculation, return 0 or use SUM(cell,10) instead of +.

Why the &"" shortcut causes new problems

You will often see =VLOOKUP(G2,A2:E8,5,FALSE)&"". Joining an empty string turns the empty result into "" and it is short, so it is popular. The catch is that it turns every result into text, including the numbers. 920 becomes the text "920".

Text numbers look right but behave differently. They sit on the left of the cell, ISNUMBER says FALSE, and SUM over a column of them ignores every one: a column of these balances totals 0. Dates are worse: Harbor Cafe's ship date of 26 Oct 2026 comes back as the text 46321, its date serial number, and no date format will change it back. Keep &"" for columns that only ever hold text, such as tracking numbers.

Dates that show 0-Jan-1900

Look up a date from an empty cell into a date-formatted cell and you get 0-Jan-1900 or 00/01/1900, depending on the format. That is the 0 again: Excel's dates count days from the start of 1900, and day 0 shows as the day before 1 January.

Use the same test, and put a word in place of the blank if that tells the reader more. Return d itself, not TEXT(d,...), so the cell keeps a real date that you can format, sort and subtract. For SO-5101 the formula returns the date 26 Oct 2026.

Hide zeros with a number format (and what it costs)

A custom number format can hide zeros without touching the formula. Formats have up to four sections, positive;negative;zero;text, and leaving the zero section empty shows nothing for 0. The cell still holds 0, so totals and averages are unchanged.

The downside is that it hides every zero, including the real ones. Oak & Ivy's paid-in-full 0 disappears along with Northside Gym's missing balance, which is the very confusion you were trying to fix. Use it for a printout where blank and zero mean the same thing, and the IF test when they do not.

1. Select the result cells and press Ctrl+1 (Format Cells).
2. Choose Custom and type 0;-0;;@ for whole numbers, or #,##0.00;-#,##0.00;;@ for money with two decimals.
3. To hide zeros on the whole sheet instead, go to File > Options > Advanced, scroll to Display options for this worksheet and clear Show a zero in cells that have zero value.

Tell "found but empty" apart from "not found"

An empty result and a missing order are different problems, and a good lookup says which one you have. SO-5199 is not in the list at all, so VLOOKUP returns #N/A. Wrap the LET formula in IFNA for the not-found case and give the empty case its own label.

With XLOOKUP, the fourth argument covers not-found only: =XLOOKUP(G2,A2:A8,E2:E8,"No such order") still returns 0 for SO-5103, because the order was found. Combine it with the empty test: =LET(r,XLOOKUP(G2,A2:A8,C2:C8,"No such order"),IF(r="","Not shipped",r)) gives Not shipped for SO-5103 and No such order for SO-5199.