Vlooklup to show a blank if not found

[psrs0810]

I have used this formula before and it has worked in the past. Why now would it show my returned value a 0 if nothing is there?

=IF(ISNA(VLOOKUP(A1,Sheet1!C:E,3,0)),"",VLOOKUP(A1,Sheet1!C:E,3,0))

 

[Jonmo1]

=IF(ISNA(VLOOKUP(A1,Sheet1!C:E,3,0)),"",VLOOKUP(A1,Sheet1!C:E,3,0))

The IF(ISNA Structure used here will return a "" if A1 is NOT found in Sheet1 Column C.
Therefore the ISNA captures it because the VLOOKUP is an error.

However, If A1 IS found in Sheet1 Column C (say it's in C20),
BUT the corresponding Value in Column E (E20) is Blank
Then the ISNA doesn't capture it, because the VLOOKUP is NOT an error
It's returning the value in E20 (blank).

So just like =A1 will return 0 if A1 is blank,
So will a vlookup.


Hope that explains it.

 

[wigi]

Are you sure the value of cell A1 is not found in column C?

Do a COUNTIF to be sure, for instance. It should give a result of 0.

 

[psrs0810]

got it - thanks guys

 

[Jonmo1]

What type of data is expeted to be returned from the vlookup?
Number or Text?

 

[psrs0810]

number.

is this formula doing the same thing?
=IFERROR(VLOOKUP(A1,Sheet1!C:E,3,0),"")

 

[Jonmo1]

number.

is this formula doing the same thing?
=IFERROR(VLOOKUP(A1,Sheet1!C:E,3,0),"")

Yes, it's the same as your IF(ISNA structure.
But the IFERROR is only available in XL2007 or higher.
was not available in XL2003