Forum Discussion

Mighty_Al's avatar
Mighty_Al
Copper Contributor
May 20, 2023

Multi-word search from text

Hello,   I have a data sheet that cotains keywords within its ID and I'm having a difficult time having excel to search for mulitple keywords per cell. I use a combination of IF and SEARCH but it o...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 20, 2023

    Mighty_Al 

    Use

     

    =IFERROR(INDEX({"redo","pass"},MATCH(TRUE,ISNUMBER(SEARCH({"redo","pass"},A2)),0)), 0)

     

    (Note that I used "pass" instead of "passive")

    Even better: replace {"redo", "pass"} with a range that contains the keywords. Much easier to maintain!

jareeymonda's avatar
jareeymonda
Copper Contributor
May 20, 2023

To search for multiple keywords within a cell in Excel, you can use an array formula with the IF, SEARCH, and ISNUMBER functions. For example, if your data is in column A and you want to search for keywords "keyword1," "keyword2," and "keyword3," you can use the formula =IF(SUM(IF(ISNUMBER(SEARCH({"keyword1","keyword2","keyword3"},A1))),1,0))>0,"Found","Not Found"). Remember to enter it as an array formula by pressing Ctrl+Shift+Enter. This formula checks if any of the keywords are found within the cell and returns "Found" or "Not Found" accordingly.

Resources