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))}
Summary 

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.

Explanation 

In this example, we have a list of of colors in a named range called "things" (E5:E9). 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))}

Get first match in cell

The language here is quite confusing, but the formula above will return the first match found in the list of things to look for. If instead you want to return the first match found in the cell being tested, you can try a formula like this:

=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))

In this version of the formula, the MATCH function is set up to look for the result of this snippet:

AGGREGATE(15,6,SEARCH(things,A1),1) // get min value

which uses the AGGREGATE function to get the minimum value in the results returned by SEARCH. We need AGGREGATE here, because the incoming array likely will contain errors (returned by SEARCH when things aren't found), and we need a function that will ignore those errors and still give us the minimum numeric value.

The result from AGGREGATE is returned directly to MATCH as the lookup value, together with the same array returned by SEARCH. The final result is the first match found in the cell, not the first match found in the list of things.

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.