VLookup

  • Thread starter Thread starter Jaz
  • Start date Start date
J

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
 
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
|
|
 
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
 
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
 
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
 
THANKS ALL FOR THE RESPONSE!!

JASPER
Pete_UK said:
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
Click to expand...
 
Back
Top