Vlookup to return blank cell when false

**omarq** · 11-23-2010, 08:21 AM

I have the following formula:

=IF(A:A="","",IF(VLOOKUP(A:A,'XXX'!H:M,2,0),"",(VLOOKUP(A:A,'XXX'!H:M,2,0))))

And i'm trying to get it to return a blank cell when the vlookup is false. It is however still returning a 0. Does anyone know where i'm going wrong here?

Thanks in advance!

**royUK** · 11-23-2010, 08:25 AM

A:A cannot be "", so I think the 0 is representing FALSE.

You need to check a cell, or use COUNTA to check for the number of cells that aren't blank

**omarq** · 11-23-2010, 09:03 AM

If i remove IF(A:A="","", is there another way of doing this?

**martindwilson** · 11-23-2010, 09:28 AM

IF(iserror(VLOOKUP(A1,'XXX'!H:M,2,false)),"",VLOOKUP(A1,'XXX'!H:M,2,false))
you cant look up a:a you need a single value to look up

**omarq** · 11-23-2010, 09:32 AM

thanks! OK. I can live with looking up an individual cell... but this formula still returns a 0.

**martindwilson** · 11-23-2010, 09:40 AM

if the cell is empty you'll get 0
take
vlookup(a1,b1:c10,2,false)
is a1 =martin and b4 = martin then you'd get whatever's is in c4 but if c4 is blank it will return 0
you could do
if(vlookup(a1,b1:c10,2,false)=0,"",vlookup(a1,b1:c10,2,false))
but its probably easier just to custom format the cells
0;-0;;@
this will hide 0