Multi-word search from text

[Mighty_Al]

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?

[#2 · Accepted solution]

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

[#3]

thank you!

[#4]

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.