where data (B5:D15), color (C5:C15), and list (J5:J7) are named ranges.
The FILTER function can filter data using a logical expression provided as the include argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:
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:
This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a logical test based on the ISNUMBER...
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...
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.