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
General;-General;

Or, repeat the formula:

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

--
Biff
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.