Exceljet

Quick, clean, and to the point

Get first match cell contains

Excel formula: Get first match cell contains
Generic formula 
{=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))}
Explanation 

To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match. In the example shown, the formula in C5 is:

{=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0))}

where "things" is the named range E5:E9.

Note: this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

In this example, we have a list of of colors in a named range called "things" (B5:B11). We want to check the text in column B to see if it contains any of these colors. If so, we want to return the name of the first color found.

Working from the inside out, this formula uses the ISNUMBER function and SEARCH function to search the text in B5 for each color listed in "things" like this:

ISNUMBER(SEARCH(things,B5)

This expression is based on a formula (explained in detail here) that checks a cell for a single substring. If the cell contains the substring, the expression returns TRUE. If not, the expression returns FALSE.  

When we give this SEARCH a list of things (instead of one thing) will give us back an array of results. Each color found will generate a numeric position, and colors not found will generate an error:

{#VALUE!;#VALUE!;20;#VALUE!;#VALUE!}

The ISNUMBER function then converts results to TRUE / FALSE values. Any number becomes TRUE, and any error (not found) becomes FALSE. The result is an array like this:

{FALSE;FALSE;TRUE;FALSE;FALSE}

This array is returned to the MATCH function as the array argument. The look up value is TRUE and the match type is set to zero to force an exact match. When there is a matching color, MATCH returns the position first TRUE found. This value is fed into the INDEX function as the row number, with the named range "things" provided as the array. When there is at least one match, INDEX returns the color at that position. When no match is found, this formula returns the #N/A error.

With hard-coded values

If you don't want to set up an external named range like "things" in this example, you can hard-code values into the formula as "array constants" like this:

{=INDEX({"red","green","blue"},MATCH(TRUE,ISNUMBER(SEARCH({"red","green","blue"},B5)),0))}
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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables