Menu
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... ?
994
At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include". In the right SUMPRODUCT, SEARCH looks for all strings in the named...
First, select cells you want to be searched. Then navigate to Home >> Editing >> Find & Select >> Find. You can also use the Ctrl + F keyboard shortcut for quick access. Click the Find All button to search the entire selected area.
To get multiple lookup values in a single cell, we need to create a function in VBA (similar to the VLOOKUP function) that checks each cell in a column and if the lookup value is found, adds it to the result.
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.
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
