• IF and VLOOKUP formula (Excel 2002)

    • This topic has 5 replies, 3 voices, and was last updated 18 years ago.
    Author
    Topic
    #442593

    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

    Viewing 1 reply thread
    Author
    Replies
    • #1065798

      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”,”yes”,”no”))

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

      Steve

      • #1065801

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

      • #1065804

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

    • #1065800

      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

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

      or

      =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"))

      Whether you need the latter, more complicated formula depends on whether the VLOOKUP may return another valid value than “x”.

    Viewing 1 reply thread
    Reply To: IF and VLOOKUP formula (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: