Exceljet

Quick, clean, and to the point

Cell contains one of many things

Excel formula: Cell contains one of many things
Generic formula 
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Explanation 

To test a cell to see if it contains one of many strings, you can use a formula based on the SEARCHISNUMBER and SUMPRODUCT functions. The formula in C5, copied down, is:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

where things is the named range E5:E9.

How this formula works

We want to test each cell in B5:B11 to see if it contains any of the strings in the named range  things (E5:E9). The formula we're using in C5, copied down, is:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

This formula based a formula (explained here) that checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE. If not, the formula returns FALSE:

ISNUMBER(SEARCH(things,B5))

However, in this case, we give SEARCH a list of strings. Since there are 5 strings in things, SEARCH returns 5 results in an array like this:

{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

When SEARCH finds a string, it returns the position of that string. If SEARCH doesn't find a string, it returns a #VALUE! error. Because "yellow" appears as the first word in B5, we see a 1. Because the other strings aren't found , the other 4 items are errors.

This array is returned directly to the ISNUMBER function. NUMBER then returns an array of TRUE / FALSE values:

{TRUE;FALSE;FALSE;FALSE;FALSE}

If we have even one TRUE in the array, we know a cell contains at least one of the strings we're looking for. The easiest way to check for TRUE is to add all values together. We can do that with SUMPRODUCT, but first we need  to force the TRUE / FALSE values to 1s and 0s with a double negative (--) like this:

--ISNUMBER(SEARCH(things,B5))

This yields a new array containing only 1s and 0s:

{1;0;0;0;0}

delivered directly to SUMPRODUCT:

=SUMPRODUCT({1;0;0;0;0})

With just one array to process, SUMPRODUCT adds the items in the array and returns a result. Any non-zero result means we have a "hit", so we add >0 to force a final result of TRUE or FALSE:

=SUMPRODUCT({1;0;0;0;0})>0 // returns TRUE

With a hard-coded list

It's not necessary to use a range for the list of strings to look for. You can also use an array constant. For example, to check for "red", "blue", and "green", you can use a formula like this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"red","blue","green"},B5)))>0

Preventing false matches

One problem with this approach is you may get false matches from substrings that appear inside longer words. For example, if you try to match "dr" you may also find "Andrea", "drink", "dry", etc. since "dr" appears inside these words. This happens because SEARCH automatically does a "contains" match.

For a quick hack, you can add space around the search words (i.e. " dr ", or "dr ") to avoid catching "dr" in another word. But this will fail if "dr" appears first or last in a cell, or appears with punctuation.

If you need a more accurate solution, one option is to normalize the text first in a helper column, taking care to also add a leading and trailing space. Then you use the formula on this page on the resulting text.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.