Excel Formula to Search for Multiple Strings Across Multiple Columns

July 25, 2017

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. 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

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