Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup - If Null (Excel 2000)

    Am attaching a worksheet if anyone would be so helpful to look at...(puleasssseeee) [img]/forums/images/smilies/smile.gif[/img]
    In cell D26 I have a formula that works correctly UNLESS I type a name that doesn't exist into the cell, then it just pulls the closer name alphabetically to its information from the lookup and throws something in the cell. I want a ZERO to appear...if that person doesn't exist.
    Thank you...Have tried several =IF(VOOKUP type formulas to no avail.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup - If Null (Excel 2000)

    By adding FALSE as fourth argument to the VLOOKUP formula, you make it return #N/A (not available) if the lookup value does not occur in the lookup list. The fourth argument whether or not to find the nearest match if there is no exact match, so FALSE forces an exact match.
    To return 0 if there is no match, combine this with the ISNA (is not available) function. It makes the formula rather cumbersome, but it works:

    =IF(ISNA(VLOOKUP(D25,A1:I22,8,FALSE)),0,VLOOKUP(D2 5,A1:I22,8,FALSE))

    or you can leave

    =VLOOKUP(D25,A1:I22,8,FALSE)

    in cell D26, and put

    =IF(ISNA(D26),0,D26)

    in cell D27.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup - If Null (Excel 2000)

    Taking it one step further, the attached spreadsheet looks up the first and last names when performing the lookup.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup - If Null (Excel 2000)

    Umm, I may post a lot in Woody's Lounge, but I am not Woody <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup - If Null (Excel 2000)

    HansV...and Tony55,

    Thank you so much for the help. Both work wonderfully, and yes Tony yours took it one step further and answered another dilemna we had for duplicate first names.

    As always, I salute you...you guys are GREAT!!!

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20> NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup - If Null (Excel 2000)

    I am sooooooooo sorry Hans...I had Woody's Lounge on my mind, and just had a brain spasm...<img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I edited the post...You have my humblest apologies....With over 16000 posts you could be! <img src=/S/mice.gif border=0 alt=mice width=50 height=25>

    Salute <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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