# Solved: Excel VLOOKUP returns 0 instead of NULL



## northco (May 8, 2012)

In Excel if the result of VLOOKUP is empty (null I assume), it returns a 0 instead of NULL.
I would prefer to have it return the null 

for example
=VLOOKUP($A$2,Results!$A$2:$U$64,5,FALSE)

The cell identified in lookup is empty. The result returned is a 0.

If there something I should be doing differently?


----------



## etaf (Oct 2, 2003)

> The cell identified in lookup is empty. The result returned is a 0.


 so it is finding the lookup but the reference in the column represented by 5 = a blank 
and you get a zero correct

my first initail reaction is to do this - OR did you want the word NULL are you going to use the NULL and so need the correct null value to be returned

=IF ( VLOOKUP($A$2,Results!$A$2:$U$64,5,FALSE) = 0 , "" , VLOOKUP($A$2,Results!$A$2:$U$64,5,FALSE)


----------



## northco (May 8, 2012)

"so it is finding the lookup but the reference in the column represented by 5 = a blank 
and you get a zero correct" <<< yes except column is empty as opposed to containing a blank. 

And I want a null/empty, not the word NULL

I thought about using something similar to your IF statement, my only concern is if the cell is legitimately 0 then I would want a 0 returned. 

But I think this does the trick
1 =IF(ISBLANK(VLOOKUP($A$2,Results!$A$2:$U$64,5,FALSE)),"",VLOOKUP($A$2,Results!$A$2:$U$64,5,FALSE))

This does confuse me a bit because if it returns blank, why does it show as 0. I verified that is really the way it was working. 

Either way, I guess I claim victory.

Thanks a lot for your help!


----------



## Garf13LD (Apr 17, 2012)

For me I just change the format of number to <blank> for zeros.


----------

