Forum Discussion

Craig10's avatar
Craig10
Copper Contributor
May 23, 2023

"Or" function not working as expected

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?

 

 

 

Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 23, 2023

    Craig10 

    Are you sure

    =IF(OR(A83="SB","CE",),"CEO",A83)

    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

    =IF(
    OR(
        A83 = "SB",
        A83 = "CE",
        A83 = "AB"
    ),
    "CEO",
    A83
)

    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.

Resources