Summary

To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use a formula based on the COUNTIF function nested in the SUMPRODUCT function.

Generic formula

=SUMPRODUCT(COUNTIF(rng,"*"&substrings&"*"))>0

Explanation 

All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this:

COUNTIF(rng,"*"&substrings&"*"))

By wrapping substrings in the asterisks, Excel evaluates the formula like this:

=SUMPRODUCT(COUNTIF(rng,{"*dog*";"*green*";"*sun*";"*every*"}))>0

COUNTIF counts the values wherever they appear in the cell. Since we are giving COUNTIF multiple values to look for, we receive a count for each value in an array like this: {1;0;1;1} .

Finally, SUMPRODUCT returns the sum of all items in the array. Any result greater than zero returns TRUE.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.