Forum Discussion
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 only works for a single keyword and when I try to add other keywords it shows the other words as #VALUE!.
Is there a way to search for multiple keywords while also utilizing IFERROR function?
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!
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!
- thank you!
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.
