If the field a vlookup is referencing is blank, how can I stop it returning "0"

[jfarlow]

Hi

I'm doing a vlookup from one sheet to another, but if the cell I'm looking up is blank, how do I prevent the cell containing the formula from showing as zero? If there's nothing in the reference cell, I want the formula cell to show blank too.....

Thanks
oj

[JBeaucaire]

Check the cell first:

=IF(ISBLANK(A1),"",VLOOKUP(A1,etc....

[jfarlow]

Sorry - I wasn't very clear in my original message.

I want to lookup an employee's ID from another sheet and return the value in another column, however in some cases there won't be a value in that column but I don't want the lookup to return 0. I want it to just leave the cell with the formula in it blank....as that cell then drives some other formulas if it isn't blank. How can I get the cell to just remain empty if there's no value to return?

Thanks

[JBeaucaire]

So, you just want a null response?

I hate having to do every vlookup calculations twice just to suppress a zero with a "", but you can. This formula just runs once to check for a zero, then runs again if no zero returns:

=IF(VLOOKUP(D2,A2:B1000,2,FALSE)=0,"",VLOOKUP(D2,A2:B1000,2,FALSE))

Having given that, I have to imagine it could be less plumbing to let the zeros come and adjust the formulas later that refer to those cells regarding zero values.

[DonkeyOte]

You could also use a Custom Format to make the 0 appear as a blank and thereby negate need for Double Evaluation (as JB says -- best avoided)
(Note: underlying value of 0 will persist in the cell itself)

I don't know the range of the values you're returning (ie +ve & -ve ... or just +ve etc...) but a standard format would be:

[=0]"";General

for a number type format

#,##0;[red](#,##0);

Both would generate a blank "appearance" for value 0