Excel Formula to Search for Multiple Strings Across Multiple Columns


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.


Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com