MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup to return blank cell

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

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


Thank you very much

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

Hi Andonny

Try this


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:


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

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

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


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)