Excel: Searching for multiple terms in a cell

excel excel-2007 excel-formula excel-2010 excel-2013
Chris J. Vargo picture Chris J. Vargo · Feb 11, 2013 · Viewed 187.8k times · Source

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... ?

Answer

teylyn picture teylyn · Feb 11, 2013

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.

Related questions

Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More