where “things” is the named range E5:E9.
Note: this is an array formula and must be entered with Control + Shift + Enter.
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:
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:
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: In this version of the formula, the MATCH function is set up to look for the result of this snippet: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
