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).

Now change your formula to:

=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.

Aladin

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)

Aladin