Forum Discussion
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"," ")))
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).
- dscheikeyBronze Contributor
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,"")