where data (C4:L6) and group (C5:L5) are named ranges. The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation. The formula in B5 is: Working from the inside out, the include argument for FILTER is a logical expression: When the logical expression is evaluated, it returns an array of 10 TRUE and FALSE values: Note: the commas (,) in this array indicate columns. Semicolons (;) would indicate rows. The array contains one value per record in the data, and each TRUE corresponds to a column where the group is “fox”. This array is returned directly to FILTER as the include argument, where it does the actual filtering: Only data in columns that correspond to TRUE make it through the filter, so the result is data for the six people in the “fox” group. FILTER returns this data in the original horizontal structure. Because we want to display results from FILTER in a vertical format, the TRANSPOSE function is wrapped around the FILTER function: The TRANSPOSE function transposes the data and returns a vertical array as a final result in cell B10. Because FILTER is a dynamic array function, the results spill into the range B10:D15. If data in data (C4:L6) changes, the result from FILTER is automatically updated.
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.
