VLookup Null values

[Guest]

I am looking up values in a Pivot Table. If the value is null, the formula
is displaying zero instead of null. Is there a way to display null when null
and zero when the value is zero?

 

[Dave Peterson]

=if(vlookup(...)="","",vlookup(...))

I am looking up values in a Pivot Table. If the value is null, the formula
is displaying zero instead of null. Is there a way to display null when null
and zero when the value is zero?

 

[Guest]

Dave,

Thank you. I thought I tried that, but I guess not. If the "If" statement
is true (=""), why doesn't the vlookup display what it looked up ("")? Why
do you need to do an IF clause to display what is in the cell anyhow? Just
wondering.

Val

 

[Dave Peterson]

Try this in a test worksheet:

Put this formula in A2:
=A1

Notice that it returns a 0. That's just the way excel works.

If you want to avoid this problem, you need to do something like:
=if(a1="","",a1)

Same thing with =vlookup()'s.

 

[Guest]

Try changing the format to Custom ;;""
That tells Excel to display zero's as "".

 

[Guest]

Try changing the format to Custom ;;""
That tells Excel to display zero's as "".