Results 1 to 6 of 6

20031121, 01:22 #1
 Join Date
 Jan 2001
 Location
 North Vancouver, Br. Columbia, Canada
 Posts
 13
 Thanks
 0
 Thanked 0 Times in 0 Posts
Can Vlookup return blanks? (Excel 2000)
If I have a blank cell referenced as a result of a vlookup, Excel returns zero. Is there a way to change this behaviour so that blanks are returned as blanks rather than as zeros?
Thanks,
Archie

20031121, 05:56 #2
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Can Vlookup return blanks? (Excel 2000)
This works for me in XL2000:
=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)="","",VLOOKUP($ A3,$A$1:$D$3,3,FALSE))

20031121, 06:05 #3
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Can Vlookup return blanks? (Excel 2000)
Does this work for you?
=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,"",VLOOKU P(LookupValue,Array,2,FALSE))

20031121, 11:03 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Can Vlookup return blanks? (Excel 2000)
You can never have a formula return a blank. A blank cell means the cell is empty. If you have a formula in a cell, the cell is not blank.
Depending on what you need you can use what Michael suggested:
=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)="","",VLOOKUP($ A3,$A$1:$D$3,3,FALSE))
Which will give a null string ("") if the "lookup cell" is blank or it returns a result of a null string.
Or even Paul's formula:
=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,"",VLOOKU P(LookupValue,Array,2,FALSE))
Which will give a null string ("") if the "lookup cell" is blank or it returns a result of zero.
If you want the results to be ignored in a chart, you must return a #N/A error, which a chart will ignore. A null or a 0 (or any other error) will plot as a zero on the chart. You can not simulate a truly "blank" entry in a plot with a formula.
Both problems have their downside. In Michael's both null and blanks will return the same thing, and in Paul's both zero and blanks will return the same thing.
If you want to return a null for null string, a zero for a zero, and something else (for example, #N/A error) for a blank, you must use a combination of the different formulas:
=IF(AND(VLOOKUP(LookupValue,Array,2,FALSE)=0,VLOOK UP(LookupValue,Array,2,FALSE)=""),NA(),VLOOKUP(Loo kupValue,Array,2,FALSE))
This will only return the #N/A error (or whatever) when the cell is blank (since that is the only time the cell can be = null and equal to zero). This is probably the closest you can get to returning a "blank". The only other way is to put a result in the formula above, and afterwards write a macro to "clear" the cells contents if it has that value. (but this would remove the formula.)
Steve

20031121, 12:33 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Can Vlookup return blanks? (Excel 2000)
<img src=/S/doh.gif border=0 alt=doh width=15 height=15>
Instead of doing 2 vlookups to check, it hit me on the way to work, just use ISBLANK <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
=IF(isblank(VLOOKUP(LookupValue,Array,2,FALSE)),na (),VLOOKUP(LookupValue,Array,2,FALSE))
The other alternative which should be mentioned is to replace all the blanks in the data with whatever value you want it to return. This will eliminate the need to do the IF statement at all and you can just use the VLOOKUP by itself
Steve

20031121, 16:05 #6
 Join Date
 Jan 2001
 Location
 North Vancouver, Br. Columbia, Canada
 Posts
 13
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Can Vlookup return blanks? (Excel 2000)
Thanks Steve, Paul & Michael. I needed to differentiate between null values and zero values in source cells, and your insight has allowed my to do this.
Cheers,
Archie