VLookup

[Jaz]

I have a report that uses VLookup to search for a match a part with a part
cost on a separate worksheet. Some of the matches have return a value of
zero because the part has no cost. Instead of having zeros all over my
page, is it possible to return a blank so that there is no value in the
cell?

I don't want all thoses zeros, I would rather just have a blank cell.

Thanks,
Jasper

 

[Niek Otten]

Hi Jasper,

=IF(YourVlookupFormula=0,"",YourVlookupFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a report that uses VLookup to search for a match a part with a part
| cost on a separate worksheet. Some of the matches have return a value of
| zero because the part has no cost. Instead of having zeros all over my
| page, is it possible to return a blank so that there is no value in the
| cell?
|
| I don't want all thoses zeros, I would rather just have a blank cell.
|
| Thanks,
| Jasper
|
|

 

[T. Valko]

Try this:

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

Or, you could use a setting to not display 0 values but this will apply to
the *entire worksheet*:

Tools>Options>View tab>Zero values

Another option is to use a custom format like: 0;-0;;@

Note that using the custom format and the menu option will suppress the zero
display but the cells will still contain zeros, you just won't see them.

Biff

 

[Peo Sjoblom]

Niek has already provided a solution, another one would be to use a custom
format in the cell(s) with the vlookup formula

General;-General;

as an example

will not display zero

 

[Pete_UK]

Yet another way is to apply conditional formatting to the cells with
the VLOOKUP formula, such that if the cell contents are equal to zero
then set the foreground colour to the same as the background colour
(usually white). The cells will contain zero, but will appear blank.

Hope this helps.

Pete

 

[Jaz]

THANKS ALL FOR THE RESPONSE!!

JASPER

Yet another way is to apply conditional formatting to the cells with
the VLOOKUP formula, such that if the cell contents are equal to zero
then set the foreground colour to the same as the background colour
(usually white). The cells will contain zero, but will appear blank.

Hope this helps.

Pete