Excel - VLOOKUP worksheet function returns zero for empty cells

Asked By Hershma on 31-Mar-09 02:05 PM
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 replied on 31-Mar-09 02:11 PM
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.
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
T. Valko replied on 31-Mar-09 02:45 PM
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

Or, repeat the formula:


Microsoft Excel MVP
Hershma replied on 31-Mar-09 06:21 PM
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.