Excel Formula to Search for Multiple Strings Across Multiple Columns
- Sean O'Shea
- Jul 25, 2017
- 1 min read
You can use a COUNTIF formula nested in a Boolean AND or OR formula to search across multiple columns for multiple strings in a single row.
In a COUNTIF formula you first specify the range to be search and then the string you're looking for. If you want to search for strings that are only part of a cell put asterisks around the strings. The result will give the number of times the strings appears in the stated range.
In this example we're using an IF / THEN formula to find winners. The first AND operator excludes awards won by players named Aaron or Spahn, and the second OR operator shows where either a MVP or Cy Young award was given. Columns A to F are searched.
=IF(AND(AND(COUNTIF(A2:F2,"*aaron*")=0,COUNTIF(A2:F2,"*spahn*")=0),OR(COUNTIF(A2:F2,"*Most Valuable*")<>0,COUNTIF(A2:F2,"*Young*")<>0)),"NL Winner","")
You can plug in your own search terms or add additional ones.