Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel.

=IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","")

This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1.

All I'd like to do is search for more than one term at a time. Anyone know how to add an OR function into this so I can search for Gingrich OR Obama OR Romney etc... ?

Solution 1:

Another way

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Gingrich","Obama","Romney"},C1)))))>0,"1","")

Also, if you keep a list of values in, say A1 to A3, then you can use

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,"1","")

The wildcards are not necessary at all in the Search() function, since Search() returns the position of the found string.

Solution 2:

Try using COUNT function like this

=IF(COUNT(SEARCH({"Romney","Obama","Gingrich"},C1)),1,"")

Note that you don't need the wildcards (as teylyn says) and unless there's a specific reason "1" doesn't need quotes (in fact that makes it a text value)

Solution 3:

This will do it for you:

=IF(OR(ISNUMBER(SEARCH("Gingrich",C3)),ISNUMBER(SEARCH("Obama",C3))),"1","")

Given this function in the column to the right of the names (which are in column C), the result is:

Romney  
Gingrich    1
Obama       1