Excel Tips & Tricks: How to Display a Blank Cell Instead of Zeros in Vlookup

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

In Excel, the normal behavior for lookup functions when the result of the lookup is a blank cell, is to display a numeric zero (0) instead of leaving the display or output cell blank. This is a problem in some cases when you do not want to show a zero.

Displaying a blank cell instead of 0 when vlookup yields a blank cell

Consider this sample screen shot below:

Sample table for vlookup demo

In this vlookup function demo, another table will be showing the columns Country Code and Calling Code like this below:

Sample lookup table

In this second table that is in a separate sheet, when a country code is entered, the calling code will be pulled out from the TABLE in the first sheet using the VLOOKUP function. The formula used is:

=IFERROR(VLOOKUP(A2,Table!$B$2:$C$17,2,FALSE),"")

Now, you will notice that for countries where Calling Codes are blank, when it is pulled out by VLOOKUP, it displays 0. How will you make VLOOKUP produce a blank instead?

The answer is in revisiting the formula. Instead of using the common or conventional formula which is

=VLOOKUP(A2,Table!$B$2:$C$17,2,FALSE)

You can append a blank string to it to make is display blank when the result is a blank cell instead of showing a zero (0).

Here is the formula for that:

=IFERROR(VLOOKUP(A2,Table!$B$2:$C$17,2,FALSE)&"","")

 

MS Office Software: