Searching for multiple values in a cell

[jwillits]

I am trying to search for multiple values in a cell and if it find one of them, I would like a text value assigned to the cell. Here is my formula so far.

=IF(ISNUMBER(SEARCH(OR("ADK","DHEA"),C1)),"MMG Supplement","")

I would like it to search C1 for the text value "ADK" or "DHEA" and if it finds either, I would like it to return value "MMG Supplement.

However, it isn't working. What is wrong with my formula?

 

[Fluff]

Hi & welcome to MrExcel
How about
=IF(OR(ISNUMBER(SEARCH("ADK",C1)),ISNUMBER(SEARCH("DHEA",C1))),"MMG Supplement","")

 

[mumps]

Maybe:
=IF(OR(ISNUMBER(SEARCH({"ADK","DHEA"},C1))),"MMG Supplement","")

 

[jwillits]

Maybe:
=IF(OR(ISNUMBER(SEARCH({"ADK","DHEA"},C1))),"MMG Supplement","")

Yep, this works. The other one would have probably worked as well, but I have more than 2 text values even though my example only shows ADK and DHEA. Thanks guys!

 

[mumps]

Glad we could help.

 

[Ingolf]

Yep, this works. The other one would have probably worked as well, but I have more than 2 text values even though my example only shows ADK and DHEA. Thanks guys!

It is not necessary OR in formula

=IF(ISNUMBER(SEARCH({"ADK","DHEA"},C1)),"MMG Supplement","")

but the formula will return "MMG Supplement" for any combination of letters that will contain the letters you are looking for.

With this formula also

=IF(OR(ISNUMBER(SEARCH({"ADK","DHEA"},C1))),"MMG Supplement","")