Forum Discussion

temporalnaut's avatar
temporalnaut
Copper Contributor
Oct 06, 2020
Solved

Find a list of words in multiple columns

Hi, please, I use this formula in Excel to find out which cells contain certain text: =IF(COUNT(SEARCH({"white","blue","red","black"},B1)),"Yes","") As you can see, the formula looks for text in ce...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 06, 2020

    temporalnaut 

    Try this formula:

     

    =IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 06, 2020

temporalnaut 

This solution uses the Excel 365 function LET to organise the solution but defined names or substituting the local variables out to get a single nested formula would work as well:

 

= LET(
  words, {"white";"blue";"red";"black"},
  matrix, SIGN(ISNUMBER(SEARCH(words,array))),
  sum, MMULT({1,1,1,1}, matrix),
  IF(sum,"Yes","-") )

 

Only the first and last rows in the image are needed, the rest serves as explanation

Resources