vlookup displays "0" if reference cell is blank?

**Back2Basics** · 01-22-2009, 12:49 PM

I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.

I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.

this is the formula that i am using:

=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))

The site name appears in C4, and is selected from another sheet in the workbook.

**mewingkitty** · 01-22-2009, 12:55 PM

Could change the last section of the formula to something along the lines of

if(VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0)=0,"",VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))

**Back2Basics** · 01-22-2009, 01:00 PM

I had tried that, it seems the logical step - but when i hit enter it just displays the formula as text.

*tried it again before sending post*

It originally did the same, then i noticed that the "IF" was lower case - Normally okay, but when i changed it to upper case it worked!

Thanks for your time Kitty

**Back2Basics** · 01-22-2009, 01:02 PM

And thank you Donkey, if that is your real name.

I didn't realise quit how useful the formating can be, cheers!

**Back2Basics** · 01-22-2009, 01:04 PM

Quote: First off let me point out that the above is really no different to your earlier formula just shortened slightly and lookup range reduced to A:E given you're returning value from E you need not reference anything beyond that.

I have an extended range because the cell below this one references from 7, below that from 9 etc until 19....

thanks for the concern though

**DonkeyOte** · 01-22-2009, 12:57 PM

=IF(OR($C$4="",$C$4="No Match"),"",VLOOKUP($C$4,Comments!$A$2:$E$295,5,0))

First off let me point out that the above is really no different to your earlier formula just shortened slightly and lookup range reduced to A:E given you're returning value from E you need not reference anything beyond that.

To answer your question... if it's purely presentational (as it would seem) you're best bet would be to use a Custom Format on the cell as opposed to double evaluating the entire VLOOKUP, eg: