Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Text If formula

Copper Contributor

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

3 Replies

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

if or.JPG 

@gaywood 

Another possibility would be with MATCH():

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

 

@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)?