SUMPRODUCT formula to find if multiple strings are contained in column of data

You can use a modified SUMPRODUCT formula to find which cells in a column contain any one of multiple strings. Follow these steps:

1. In this example we have listed the strings we want to search for in column R. We select the strings and then in the name box on the upper left designated a name for the group of strings. The names:

Hodges

Robinson

Reese

Campanella

. . . are given the name 'Bums'.

2. Now in a helper column to the right of the data we enter this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(Bums,A2)))>0

We search for each string listed in 'Bums' in the first column, A.

3. When any part of the entry in column A for a particular row lists the string, the value TRUE is returned.