Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    ISNA Problem (Excel 2002)

    Hi

    This has been explained to me several times but I still can't get it correct

    =IF(ISNA($AC$1="Batch 1",VLOOKUP(AA7,Pivotot,3,0)),"",IF($AC$1="Batch 1",VLOOKUP(AA7,Pivotot,3,0)))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: ISNA Problem (Excel 2002)

    Generally speaking, the idea is: you have a part of a formula, let's say
    <code>
    <big>=F</big>
    </code>
    but under some circumstances, F could result in #N/A. So you test for that:
    <code>
    <big>=IF(ISNA(F),"",F)</big>
    </code>
    which says "if F results in #N/A, return a blank "", else return the result of F".

    In your formula, the role of F is played by
    <code>
    VLOOKUP(AA7,Pivotot,3,0)
    </code>
    As you should know by now, VLOOKUP will return #N/A if there is no match. To hide #N/A, you use
    <code>
    IF(ISNA(VLOOKUP(AA7,Pivotot,3,0)),"",VLOOKUP(AA7,P ivotot,3,0))
    </code>
    However, this "F" is embedded in a larger formula:
    <code>
    =IF($AC$1="Batch 1",F,"?")
    </code>
    You haven't really specified what you want to return if $AC$1 does NOT equal "Batch 1", let's assume that you want a blank:
    <code>
    =IF($AC$1="Batch 1",F,"")
    </code>
    Now substitute the modified version of F that includes the test:
    <code>
    =IF($AC$1="Batch 1",IF(ISNA(VLOOKUP(AA7,Pivotot,3,0)),"",VLOOKUP(AA 7,Pivotot,3,0)),"")
    </code>
    This looks complicated, but it can be done by building the formula step by step, instead of trying to write it in one go.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ISNA Problem (Excel 2002)

    Hi Hans

    Thanks for explanation, I will endeavor to try and get my head around it.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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