Vlookup formula returns value of 0 from cell with blank cell contents

**nander** · 06-27-2010, 02:28 PM

I'm using this VLOOKUP formula
=IF(ISNA(VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE))," ",(VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)))

if there is a value in the WITCRX worksheet the valued is returned however if the cell in WITCRX is blank the value returned is 0,. I don't undertand why. What do I need to change in the WITCRX cells with no value?

**oldchippy** · 06-27-2010, 03:05 PM

Hi,

Here's an excellent video here on vlookup from Daniel Ferry

http://www.excelhero.com/blog/2010/0...---part-1.html

**nander** · 06-27-2010, 04:41 PM

Thanks for the link. I'm looking for an answer vs watching a 27 min video on vlookup. I don't have time to view the video. Anyone else have any ideas?

**martindwilson** · 06-27-2010, 04:48 PM

thats correct a blank cell = 0
try =a1 on a spare sheet where a1 is blank some ways to hide 0 here
http://office.microsoft.com/en-us/ex...010070461.aspx

**oldchippy** · 06-27-2010, 04:55 PM

Originally Posted by nander

Thanks for the link. I'm looking for an answer vs watching a 27 min video on vlookup. I don't have time to view the video. Anyone else have any ideas?

you can't be that busy, its still the weekend

**daddylonglegs** · 06-27-2010, 05:54 PM

One option is to concatenate "" to the result, i.e.

=IF(ISNA(VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)),"",VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)&"")

That will alleviate your problem, although it also converts any numeric value returned by the VLOOKUP to a text value. If you don't want that you can go with the longer

=IF(ISNA(VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)),"",IF(VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)="","",VLOOKUP($A23649,WITCRX!$A$2:$C$64321,3,FALSE)))