Vlookup returns a "0"

[mark.wolven]

I am using a vlookup that works mostly as I expect. That is, when
there is a value to be returned, it returns the value - however, there
are instances where no value will be returned, in some cases, Excel
leaves the cell blank (as I prefer), in other cases, it populates the
cell with a 0 (zero).

What can I do to not have it populate with a zero? And why the
discrepancy between the two behaviors?

 

[Niek Otten]

Please post your formula and examples of your table and search argument. How is the formula cell formatted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am using a vlookup that works mostly as I expect. That is, when
| there is a value to be returned, it returns the value - however, there
| are instances where no value will be returned, in some cases, Excel
| leaves the cell blank (as I prefer), in other cases, it populates the
| cell with a 0 (zero).
|
| What can I do to not have it populate with a zero? And why the
| discrepancy between the two behaviors?

 

[mark.wolven]

Please post your formula and examples of your table and search argument. How is the formula cell formatted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I am using a vlookup that works mostly as I expect. That is, when
| there is a value to be returned, it returns the value - however, there
| are instances where no value will be returned, in some cases, Excel
| leaves the cell blank (as I prefer), in other cases, it populates the
| cell with a 0 (zero).
|
| What can I do to not have it populate with a zero? And why the
| discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =vlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =SLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank.

 

[Pete_UK]

You can do it like this:

=IF(vlookup($a26,NDOLU,4,false)="","",vlookup($a26,NDOLU,4,false))

That way if the return value is actually a zero rather than a blank
then a zero will be displayed. If you don't want a zero to be
displayed, then do it like this:

=IF(vlookup($a26,NDOLU,4,false)=0,"",vlookup($a26,NDOLU,4,false))

which will trap both blanks and zeroes.

Hope this helps.

Pete

 

[Peo Sjoblom]

You can do something like this

=IF(vlookup($a26,NDOLU,4,false)="","",vlookup($a26,NDOLU,4,false))

you can also use a custom format for this particular cell

General;-General;;

or if it is number format

0.00;-0.00;;

of course that will hide "real" zeros as well which the former won't

--


Regards,


Peo Sjoblom

Please post your formula and examples of your table and search argument.
How is the formula cell formatted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

message
|I am using a vlookup that works mostly as I expect. That is, when
| there is a value to be returned, it returns the value - however, there
| are instances where no value will be returned, in some cases, Excel
| leaves the cell blank (as I prefer), in other cases, it populates the
| cell with a 0 (zero).
|
| What can I do to not have it populate with a zero? And why the
| discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =vlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =SLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank.

 

[Sandy Mann]

To answer the other part of your question, I think that if the cell is
truely empty then VLOOKUP() will return 0 but if it only looks blank because
formula is returning "" then that "" will return the "" and the cell will
look like it is remaining blank.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Please post your formula and examples of your table and search argument.
How is the formula cell formatted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

message
|I am using a vlookup that works mostly as I expect. That is, when
| there is a value to be returned, it returns the value - however, there
| are instances where no value will be returned, in some cases, Excel
| leaves the cell blank (as I prefer), in other cases, it populates the
| cell with a 0 (zero).
|
| What can I do to not have it populate with a zero? And why the
| discrepancy between the two behaviors?

I know that I can go to the Tools Options and turn off the "Zero
Values" and in this case, that is fine. But what if I want to display
zeros on the future.

OK, the formula in use is: =vlookup($a26,NDOLU,4,false)
where NDOLU is a range defined as: =SLWInput!$A$2:$AX$340

And the fourth column in the range is formatted as "General" - the
column should be populated with a value of "Y" or be blank.