VLOOKUP worksheet function returns zero for empty cells

[Hershmab]

I am using VLOOKUP to get address information from one "master" worksheet to
another. Some of the master cells are empty (not blank); the value returned
by VLOOKUP in such cases is zero, not blank.

How can I prevent that without calling the function twice in each formula?

 

[Luke M]

If you don't want to change the VLOOKUP, change the master data.

Select cells in question.
Bring up Find & Replace (Ctrl+H)

Leave first line blank
In second line, input:
=""

Under options, select match entire cell contents. Then replace all. Your
formula should now return a blank when it finds a "blank" cell.

 

[T. Valko]

If the data being returned is text you could use a custom format that
suppresses the display of 0 values. The cell will still contain a numeric 0
it just won't be displayed making the cell appear blank. Otherwise, you
either have to repeat the formula or fill the empty cells with blanks.

To suppress the display of 0:

Format>Cells>Number tab>Custom
General;-General;

Or, repeat the formula:

=IF(VLOOKUP(...)="","",VLOOKUP(...))

 

[Hershmab]

Unfortunately the first idea will not apply in my circumstances. I am using
this result in a Word Mailmerge where the 0 will be displayed regardless of
its Excel format.