SOLVED

Nested IF and OR functions not returning correct values

Copper Contributor

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

4 Replies

@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,"")

 

@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.
best response confirmed by klou0056 (Copper Contributor)
Solution

@klou0056 

It looks good to me:

dscheikey_0-1718983344009.png

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).

 

Ah yep, it's working now. Thank for the help!
1 best response

Accepted Solutions
best response confirmed by klou0056 (Copper Contributor)
Solution

@klou0056 

It looks good to me:

dscheikey_0-1718983344009.png

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).

 

View solution in original post