Forum Discussion

gaywood's avatar
gaywood
Copper Contributor
Sep 20, 2022

Text If formula

I am trying to create a formula with an IF function and OR for around 37 different text options with 1 text result. EG =IF(A5=OR("ABC","DEF","GHI"AND SO ON),"XYZ","0")

But am coming up with a #VALUE error. I'm not sure if I am typing the formula wrong or if I am showing too many arguements.

Please would some be able to offer any better formula options or advice?

 

Thanks

Formulas and Functions
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Sep 20, 2022

    gaywood 

     

    And yet another alternative that is closer to your original attempt:

     

    =IF(OR(A5={"ABC","DEF","GHI",...etc...}), "XYZ", "0")

     

    Caveat:  Are you sure that you want the string "0", not the number zero (without double-quotes)?

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 20, 2022

    gaywood 

    Another possibility would be with MATCH():

    =IF(ISNUMBER(MATCH(A5,({"ABC","DEF","GHI"}),0)),"XYZ","0")

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 20, 2022

    gaywood 

    =IF(OR(A5="ABC",A5="SDF",A5="ERT",A5="WSD",A5="JJJ",A5="HZU",A5="FTB",A5="XLO"),"XYZ","0")

    You can simply use IF(OR .

     

Resources