# vlookup to return blank cell

Posted by Andonny on April 27, 2001 5:26 AM

Hi,
I would like to return a blank cell if vlookup finds a blank cell. This formula returns 0 if the cell is blank.

=IF(ISNA(VLOOKUP(A2,Sheet!\$A\$1:\$B\$10,2,FALSE)),0,VLOOKUP(A2,Sheet1!\$A\$1:\$B\$10,2,FALSE))

Thank you very much
Andonny

Posted by Jerid on April 27, 2001 5:37 AM

Hi Andonny

Try this
=IF(ISNA(VLOOKUP(A2,Sheet!\$A\$1:\$B\$10,2,FALSE)),"",VLOOKUP(A2,Sheet1!\$A\$1:\$B\$10,2,FALSE))

Jerid

Posted by Aladin Akyurek on April 27, 2001 5:46 AM

Replace 0 by "".

May I suggest a change or two to the formula?

1) Select the range A1:B10 and give it a name via the Name Box, say, DATA.
2) Select the range A1:A10 and give it a name, say, LVALUES (for lookup values).

=IF(ISNUMBER(MATCH(A2,LVALUES,0)),VLOOKUP(A2,DATA,2,0),"")

This formula is probably cheaper (more efficient)than the original formula.

Note. The 4th arg of VLOOKUP is 0, which is equivalent to FALSE.

Posted by Andonny on April 27, 2001 6:08 AM

Hi,
This is an interesting case. I just tried all formulas and I figured that when I just have a blank cell not being touched bofore it still returns 0. As soon as I type anything into the cell and then make it blank then it returns a blank cell.

Thanks a lot
Andonny

Posted by Aladin Akyurek on April 27, 2001 6:13 AM

Andonny,

I began to think after my response that this must be something else:

I suggest that you wrap your original formula (or the revised formula that I suggested) into:

=IF(isblank(A2),"",the original/revised formula)