VLOOKUP worksheet function returns zero for empty cells
 

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?

VLOOKUP worksheet function returns zero for empty cells
 

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!*


"Hershmab" wrote:

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?

VLOOKUP worksheet function returns zero for empty cells
 

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:

FormatCellsNumber tabCustom
General;-General;

Or, repeat the formula:

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

--
Biff
Microsoft Excel MVP


"Hershmab" wrote in message
...
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?

VLOOKUP worksheet function returns zero for empty cells
 

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.

"T. Valko" wrote:

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:

FormatCellsNumber tabCustom
General;-General;

Or, repeat the formula:

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

--
Biff
Microsoft Excel MVP


"Hershmab" wrote in message
...
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?

This problem was getting the best of me and I couldn't find any other suggestions that worked. Thanks!