Forum Discussion

klou0056's avatar
klou0056
Copper Contributor
Jun 21, 2024

Nested IF and OR functions not returning correct values

I'm trying to set up an IF function to check data in a column and insert specific values in another. I've nested other OR and IF functions within it, but the data keeps returning true even if it is false. I don't see any obvious errors. Any thoughts? This is my current function:

 

=IF(OR($C2:$C131="ADMIN",$C2:$C131="PLANT"),"CAT1",IF(OR($C2:$C131="ENDO",$C2:$C131="ONC",$C2:$C131="VAS",$C2:$C131="PLASTIC",$C2:$C131="SURG",$C2:$C131="MIS"),"CAT2",IF(OR($C2:$C131="CARDIO",$C2:$C131="COLOR",$C2:$C131="LARY",$C2:$C131="TRUM",),"CAT3"," ")))

  • klou0056 

    It looks good to me:

    In older Excel versions you have to save this formula as a matrix formula.

    Please have a look at the enclosed file to see if it works there (as it did for me).

     

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    klou0056 

    I have only discovered one small error. There is one comma too many after "TRUM".


    But I would simplify the function.

    =IFS(OR($C2:$C131={"ADMIN","PLANT"}),"CAT1",
    OR($C2:$C131={"ENDO","ONC","VAS","PLASTIC","SURG","MIS"}),"CAT2",
    OR($C2:$C131={"CARDIO","COLOR","LARY","TRUM"}),"CAT3",
    TRUE,"")

     

    • klou0056's avatar
      klou0056
      Copper Contributor
      dscheikey
      Nice catch and thank you for the simplification. Unfortunately, the output still comes out as "CAT2" so it seems that something else is amiss.
      • dscheikey's avatar
        dscheikey
        Bronze Contributor

        klou0056 

        It looks good to me:

        In older Excel versions you have to save this formula as a matrix formula.

        Please have a look at the enclosed file to see if it works there (as it did for me).

         

Resources