VLOOKUP Return Blank Instead of 0

When VLOOKUP finds a match but the cell it should return is empty, it shows 0. To make VLOOKUP return a blank instead of 0, you check the result with IF and swap an empty result for empty text (""). This guide shows that fix, two shorter alternatives, and how to handle IDs that aren't found at all.

Why does VLOOKUP return 0 for an empty cell?#

A formula always has to return something, and Excel shows an empty result as 0. VLOOKUP finds the row, reads the cell, and passes back its value. When the cell is empty, the formula cell displays 0.

Here is a phone list where two people have no extension:

With 102 in E2, this formula returns 0:

=VLOOKUP(E2,A2:C6,3,FALSE)

The 0 is misleading: Tom doesn't have extension 0, he has none. With dates it is worse. An empty date cell comes back as 0, which Excel may display as 00/01/1900. If VLOOKUP itself is new to you, read the VLOOKUP guide or the VLOOKUP function reference first.

Fix 1: wrap VLOOKUP in IF#

Run the lookup once to test it, and again to return the value. If the result equals "", show "":

=IF(VLOOKUP(E2,A2:C6,3,FALSE)="","",VLOOKUP(E2,A2:C6,3,FALSE))

For 102, the cell now looks empty. For 101, it shows 2041, still as a real number you can calculate with. Read it as: "If the lookup is empty, show nothing, otherwise show the lookup."

You can show a word instead of a blank, such as "No extension", by changing the middle argument. The IF function guide explains the three arguments.

Note that the test uses ="", not ISBLANK. VLOOKUP hands back a value, not the cell itself, so ISBLANK(VLOOKUP(...)) returns FALSE even when the source cell is empty.

Fix 2: add &"" to the end#

Joining empty text to the result is the shortest fix:

=VLOOKUP(E2,A2:C6,3,FALSE)&""

An empty cell becomes "", so the result looks blank. The catch: every result becomes text. For 101 you get the text "2041", not the number 2041. That is fine for names, codes and extensions, but SUM ignores text, and comparing it with a number fails. Avoid this trick for amounts, quantities and dates.

Fix 3: use LET to look up only once#

In Microsoft 365 and Excel 2021, LET stores the lookup in a name, so Excel runs it only once:

=LET(x,VLOOKUP(E2,A2:C6,3,FALSE),IF(x="","",x))

The result is the same as Fix 1, and the formula is shorter and faster on big sheets. If a colleague opens the file in Excel 2019 or older, LET shows #NAME?, so use Fix 1 for shared files.

Fix 4: INDEX and MATCH with ISBLANK#

INDEX returns a reference to the cell, not just its value, so ISBLANK works on it:

=IF(ISBLANK(INDEX(C2:C6,MATCH(E2,A2:A6,0))),"",INDEX(C2:C6,MATCH(E2,A2:A6,0)))

This gives the same result as Fix 1. It is useful if your sheet already uses INDEX and MATCH, or if you want ISBLANK's strict meaning: TRUE only for a truly empty cell. The lesson on IS functions shows how ISBLANK, ISNUMBER and ISTEXT check what is really in a cell.

XLOOKUP behaves like VLOOKUP here: it also shows 0 for an empty result. Wrap it in IF in the same way, or add &"".

Fix 5: hide zeros with a number format#

If you only need the zeros to disappear on screen, change the format, not the formula. Select the cells, press Ctrl+1 (Mac: ⌘1), choose Custom, and enter:

0;-0;;@

The third part, between the second and third semicolons, is the format for zero. Leaving it empty hides zeros. The value is still 0, so SUM and other formulas treat it as 0.

This hides real zeros too. If a true 0 means something, such as a stock level, use Fix 1 instead. Pivot tables have a similar problem with empty cells, covered in showing zero in empty pivot cells.

How to handle blanks and #N/A together#

A blank result and a missing ID are different problems. An empty cell gives 0; an ID that isn't in the list gives #N/A. Handle both by wrapping Fix 1 in IFNA:

=IFNA(IF(VLOOKUP(E2,A2:C6,3,FALSE)="","",VLOOKUP(E2,A2:C6,3,FALSE)),"Not found")

For 104 (Omar) the cell is blank, for 101 it shows 2041, and for 999 it shows Not found. Use IFNA rather than IFERROR, so that real mistakes, such as a wrong column number, still show up. Keep FALSE as the last VLOOKUP argument, too: the article on VLOOKUP exact match explains why. If you return several columns at once, the same fix applies to each one; see VLOOKUP with multiple columns.

Which fix should you use?#

Choose the fix by what the column holds and who will open the file.

Two mistakes are common. The first is writing the test as =IF(VLOOKUP(...)=0,"",...). That also hides real zeros, such as a stock count of 0, so test for "" instead. The second is leaving out FALSE in VLOOKUP. Without it, VLOOKUP uses an approximate match and can return a value from the wrong row, which no blank fix will catch.

Practise it#

Clean lookup results make a report easy to trust. Practise VLOOKUP in a live spreadsheet: you write each lookup and the lesson checks it instantly. Then practise ISBLANK and the IS functions to test what a cell really holds before you show it.