Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    IF and VLOOKUP formula (Excel 2002)

    Hi,
    I'm having a problem with the following formula:

    =IF(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE)="x","yes", "no")

    It is giving a result of "yes" if the vlookup is an "x", however all my other values show up as #N/A, as opposed to my expectations of it being a "no" (as this is the "otherwise" part of the formula). I've tried playing around with my parenthesis, but have had no success. Anyone know what I'm doing wrong?

    Thanks!
    Lana

  2. #2
    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: IF and VLOOKUP formula (Excel 2002)

    If the item A2 in not in the list, the Vlookup will yield an error and thus the result will be an error

    You can try:
    =IF(iserror(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE))," no",IF(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE)="x","ye s","no"))

    If not found it will give a no. If found and = x then a yes, otherwise a no

    Steve

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

    Re: IF and VLOOKUP formula (Excel 2002)

    If the value of A2 cannot be found in list!K2!M10, VLOOKUP will return #N/A (not available), and all formulas using this will return #N/A too. Try
    <code>
    =IF(ISNA(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE)),"no" ,"yes")
    </code>
    or
    <code>
    =IF(ISNA(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE)),"no" ,IF(VLOOKUP(A2,list!$K$2:$M$10,3,FALSE)="x","yes", "no"))
    </code>
    Whether you need the latter, more complicated formula depends on whether the VLOOKUP may return another valid value than "x".

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: IF and VLOOKUP formula (Excel 2002)

    Yep, this makes sense!! I knew I was overlooking the obvious... thanks Hans!
    Lana

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

    Re: IF and VLOOKUP formula (Excel 2002)

    It was Steve, not I!

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: IF and VLOOKUP formula (Excel 2002)

    This works great Steve... thanks for your help!!
    Lana

Posting Permissions

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