Vlookup returns a zero?

R

Richard

Hi,

I'm using vlookup to return a value, if there is one, from another
worksheet. The problem I'm encountering is that vlookup will return a zero if
there is no value to return. How do I get to return an empty cell if it
cannot find a value to return?


Thank You,
 
N

njuneardave

Suppose if you are looking for a value....and ur search key is in B1 of
Sheet1 and youre looking for it somehwere in column B of Sheet2 (between row
2 and 1000)....

=IF(ISNA(VLOOKUP(Sheet1!B1,Sheet2!$B$2:$B$1000,1,FALSE)),IF(ADataInputTable!B1 = "","", ADataInputTable!B1),VLOOKUP(Sheet1!B1,Sheet2!$B$2:$B$1000,1,FALSE))

that way, if VLOOKUP doesn't find the value, and the value it searched for
is an empty cell, it will return the empty blank....but if the value it
serached for was not empty, it will return that value.
 
K

Ken Hudson

Hi Richard,
I believe that VLOOKUP will return a 0 if it finds the "Lookup" reference
and the cell to be returned is empty. (Of course, it will also return a 0 if
there is a zero in the cell.)

This formula should change the 0 to an empty cell:

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