Results 1 to 6 of 6
  1. #1
    New Lounger
    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

  2. #2
    3 Star Lounger
    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))

  3. #3
    3 Star Lounger
    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))

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    New Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •