"Or" function not working as expected

Copper Contributor

I'm probably doing something wrong here, but for the life of me I can't figure out what it is.


For this formula =IF(OR(A83="SB","CE",),"CEO",A83), if the contents of the cell in column A are either "SB" or CE", the cell in column B returns "CEO". Otherwise, it returns whatever is in column A.



When I add a third condition, =IF(OR(A83="SB","CE", "AB"),"CEO",A83), the "OR" condition is ignored, and the formula returns whatever is in column A - even if it's "SB", "CE", or "AB".



What am I doing wrong?




2 Replies




It works with the { } in my sheet.



Are you sure


returns "CEO" if in A83 is "CE" ? It shall return "CE".

OR() returns TRUE if at least one of conditions is TRUE. Conditions are evaluating one by one, actually till first TRUE. For above

1st condition: A83="SB" , returns FALSE

2nd condition: "CE" , returns "CE" which is not equal to TRUE.

Thus we have no one condition which returns TRUE.


More correct notation for the conditions will be

        A83 = "SB",
        A83 = "CE",
        A83 = "AB"

each of them compare value in A83 with some text.

@OliverScheurich suggested more compact form. Texts to compare are packed into array, with that OR evaluates elements of the array one by one compare them with A83.