where data (B5:D15), color (C5:C15), and list (J5:J7) are named ranges. MATCH is configured to look for each color in C5:C15 inside the smaller range J5:J7. The MATCH function returns an array like this: Notice numbers correspond to the position of “found” colors (either “red”, “blue”, or “black”), and errors correspond to rows where a target color was not found. To force a result of TRUE or FALSE, this array goes into the ISNUMBER function, which returns: The array above is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a TRUE value.
With hardcoded values
The example above is created with cell references, where target colors are entered in the range J5:J7. However, by using an array constant, you can hardcode values into the formula like this with the same result:
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.
