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?

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?

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?

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!