Forum Discussion

B-Love's avatar
B-Love
Occasional Reader
Apr 30, 2026
Solved

Triple nested if statement fails on different column

I'm working on an audit template. Auditors are texting websites against a standard. The standard is listed on a sheet named 'Formula Values' and a column in the 'Findings' sheet has a drop-down driven by the standard list. Typically the report lists failures on the Findings page. I have been requested to also list what standards have passed and which were not applicable in the context of the particular audit. 

I created a list of the most-likely not-applicable standards on the 'Test Target' sheet. All the standards are listed on the 'Formula Values' sheet, so I added a column named Pass/Fail/Not Applicable. I've written a formula that first checks if any of the standards on the list appear in the 'Findings' sheet. If yes, "Failed" is written in the cell. If no, another IF checks if any of the standards on the list do not appear in the 'Findings' sheet. If yes, "Passed" is written in the cell. If no, another IF checks if any of the standards on the list appear in the list of not applicable standards in the 'Test Target' sheet. 

This last check fails. If I extract the IF statement and put it by itself in a cell, it correctly compares each standard with the list of not applicable standards and writes "N/A" in the cell for those standards so found. If I put it back in the triple nested IF it fails.

I need to know how to get this to work, and I need to know what to do with the final "does not match" so it doesn't overwrite any previous "Failed" or "Passed" values.

=IF(COUNTIF('Test Target'!K2:K50,'Formula Values'!B2:B92)>0, "N/A", IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)>0,"Failed",

IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2:B92)>0,"N/A","")

))

  • HelloB-Love​,

    Your formula fails because the logic order makes the N A condition unreachable. The second IF checks COUNTIF equals zero and returns Passed for anything not found in Findings, so Excel never evaluates the N A condition.

    Also COUNTIF should compare a range to a single cell, not a range to a range. Use B2 and fill down instead of B2 colon B92.

    Correct formula
    =IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2)>0,"N/A",IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2)>0,"Failed","Passed"))

    This checks N/A first then Failed and returns Passed as the default.

4 Replies

  • B-Love's avatar
    B-Love
    Occasional Reader

    Sorry, here is the actual formula I'm asking about:

    =IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)>0,"Failed", IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)=0,"Passed",

    IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2:B92)>0,"N/A","")

    ))

  • B-Love's avatar
    B-Love
    Occasional Reader

    First, I should have written "test" instead of "text." Second, I've fiddled with the formula so much that the formula I put in the original post was not the one I'm asking about. Here is the corrected formula that I am asking about:

    =IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)>0,"Failed", IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2:B92)=0,"Passed",

    IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2:B92)>0,"N/A","")

    ))

    • Olufemi7's avatar
      Olufemi7
      Iron Contributor

      HelloB-Love​,

      Your formula fails because the logic order makes the N A condition unreachable. The second IF checks COUNTIF equals zero and returns Passed for anything not found in Findings, so Excel never evaluates the N A condition.

      Also COUNTIF should compare a range to a single cell, not a range to a range. Use B2 and fill down instead of B2 colon B92.

      Correct formula
      =IF(COUNTIF('Test Target'!K2:K80,'Formula Values'!B2)>0,"N/A",IF(COUNTIF(Findings!G2:G500,'Formula Values'!B2)>0,"Failed","Passed"))

      This checks N/A first then Failed and returns Passed as the default.