Exceljet

Quick, clean, and to the point

Cell contains which things

Excel formula: Cell contains which things
Generic formula 
=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,A1)),""))
Summary 

To check a cell for several things at once, and return a list of things found, you can use a formula based on the TEXTJOIN and FILTER functions, with help from SEARCH and ISNUMBER. In the example shown, the formula in C5, copied down, is:

=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))

where things is the named range E5:E9.

Explanation 

In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found.  In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:

 =TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))

Working from the inside out, this formula is based on the formula described here, which uses the SEARCH function together with the ISNUMBER function:

ISNUMBER(SEARCH(things,B5))

In a nutshell, SEARCH returns a number if it finds the target string and an error if not, and ISNUMBER converts this result into a TRUE or FALSE. In this case, we are looking for several strings at once – the five colors in the named range things (E5:E10), so the code above returns an array with five TRUE FALSE values like this:

{FALSE;FALSE;TRUE;FALSE;TRUE}

Notice the TRUE values correspond to white and green in the list of colors (positions 3 and 5), and FALSE values correspond to the colors not found. This array is returned directly to the FILTER function as the include argument, with the array argument given as things:

FILTER(things,{FALSE;FALSE;TRUE;FALSE;TRUE},"")

The FILTER function uses the TRUE and FALSE values to filter the list of colors and returns an array of the two colors found:

{"white";"green"} // result from FILTER

The result from FILTER is delivered to the TEXTJOIN function:

=TEXTJOIN(", ",1,{"white";"green"})

TEXTJOIN is configured to join items in the array with a comma and space (", ") and to ignore empty strings (""). The number 1 is provided instead of TRUE for brevity. TEXTJOIN concatenates the found colors, separated by commas, and returns a final result.

Note FILTER's not_found argument is provided as an empty string (""). In the event no colors are found, FILTER returns an empty string to TEXTJOIN, which then also returns an empty string as the final result.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.