Forum Discussion

learner1234's avatar
learner1234
Copper Contributor
Aug 24, 2022

Finding Text in Arrays, combining 2 arrays

Hello,

 

I would like to simplify the following function:

 

=IF(OR(--(COUNTIF(A2:E2;"*text1*")>0);--(COUNTIF(A2:E2;"*text2*")>0));"Yes";"No")

 

Goal: OR function that refers to an array with *text1* *text2*

 

Maybe someone can help me, thank you so much!

Lars

excel
Formulas and Functions

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 24, 2022

    learner1234 You could certainly shorten it.

    =IF(COUNTIF(A2:E2;"*text1*")+COUNTIF(A2:E2;"*text2*");"Yes";"No")

     

    In case you ever need to expand the formula with more text matches, perhaps this one is a bit easier to maintain:

    =IF(SUM(IFERROR(FIND({"text1";"text2"};A2:E2),0));"Yes","No")

Resources