Results 1 to 8 of 8
  1. #1
    BakerMan
    Guest

    Vlookup #NA (2000)

    Attached is a small spreadsheet that I use vlookup to find data. I also use data validation. The vlookup searches for the selection from the drop down. If there is no selection in the drop down cell, I get a #NA . Can I modify the vlookup formula so that the cell is blank if the data validation cell is blank?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Vlookup #NA (2000)

    Hi,

    You could do that by incorporating an error check, as in:
    =IF(ISERROR(VLOOKUP(C3,I3:J5,2,FALSE)),"",VLOOKUP( C3,I3:J5,2,FALSE))

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    BakerMan
    Guest

    Re: Vlookup #NA (2000)

    That did what I needed thankyou............

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup #NA (2000)

    =IF(C3="","",VLOOKUP(C3,I3:J5,2,0))

    Why ISERROR? Wouldn't ISNA be sufficient?

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

    Re: Vlookup #NA (2000)

    Perhaps ISNA makes it clearer which error you're trying to suppress, but ISERROR will work as well.

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup #NA (2000)

    Granted ISERROR works, but what other error does Vlookup return? You're already performing Vlookup twice, looking to suppress all the error messages, plus suppressing any syntax errors. Just my opinion on ISERROR.....

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

    Re: Vlookup #NA (2000)

    The only other error VLOOKUP could return (I think) is #REF, if you accidentally deleted the lookup value or lookup table.

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup #NA (2000)

    Yep, #REF and using a column to return not within the lookup table, but ISERROR would hide that too......My apologies for the rantings and ravings on ISERROR.......

Posting Permissions

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