Get first match cell contains
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:
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.
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:
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:
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: