Which formula would provide a dynamic array showing a unique list of categories in a products table where the group is “clothing”?

Answer: =FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")

In Microsoft Excel, dynamic arrays enable users to work with multiple values simultaneously, simplifying the process of creating and managing lists or tables. One of the most powerful features of dynamic arrays is their ability to generate dynamic results based on formulas. In this explanation, we will examine a formula that provides a dynamic array showing a unique list of categories in a products table where the group is “clothing.” This formula combines two functions: FILTER() and UNIQUE().

Dynamic Arrays and Functions

Dynamic Arrays

Dynamic arrays are a powerful feature in Excel that allows formulas to output multiple values simultaneously. Instead of outputting a single value, dynamic arrays “spill” the result into a range of cells, making it easy to work with large amounts of data. The size of the array adjusts automatically based on the formula and the source data, ensuring that the output remains up-to-date as the data changes.

FILTER() Function

The FILTER() function is a dynamic array function that filters a range or an array based on the criteria specified in the formula. It returns a new array containing only the rows or columns that meet the given conditions. The syntax for the FILTER() function is as follows:

=FILTER(array, include, [if_empty])

• array: The range or array to filter.
• include: The criteria to apply to the array. The function will return only the rows or columns for which this argument evaluates to TRUE.
• if_empty: (Optional) The value to return if no rows or columns meet the criteria. If this argument is not provided, the function will return an error if no rows or columns meet the criteria.

UNIQUE() Function

The UNIQUE() function is another dynamic array function that returns a unique list of values from the specified range or array. It removes duplicate values and returns only distinct elements. The syntax for the UNIQUE() function is as follows:

=UNIQUE(array, [by_col], [exactly_once])

• array: The range or array from which to extract unique values.
• by_col: (Optional) A logical value that specifies whether the unique values should be extracted by row or by column. If TRUE, the function will return unique values by column; if FALSE or omitted, the function will return unique values by row.
• exactly_once: (Optional) A logical value that specifies whether to return only values that occur exactly once in the array. If TRUE, the function will return only values that occur exactly once; if FALSE or omitted, the function will return all unique values.

The Formula

To create a dynamic array showing a unique list of categories in a products table where the group is “clothing,” we can use the following formula:

=FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")

This formula combines the FILTER() and UNIQUE() functions to generate the desired output. Let’s break down the formula step by step to understand how it works:

1. UNIQUE(productsTable[Category]): This part of the formula extracts a unique list of categories from the Category column in the products table. The UNIQUE() function ensures that the list contains only distinct category values, eliminating any duplicates.
2. productsTable[Group]="clothing": This part of the formula serves as the criteria for the FILTER() function. It checks if the Group column in the products table contains the value “clothing.” The FILTER() function will return only the rows for which this condition is TRUE.
3. =FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing"): Combining the two parts, this formula first obtains a unique list of categories using the UNIQUE() function, and then filters the resulting array based on the specified criteria (“clothing” group) using the FILTER() function. The output is a dynamic array containing a unique list of categories in the products table where the group is “clothing.”

Example

Let’s consider a hypothetical products table with the following columns: ProductID, ProductName, Category, Group, and Price.

In this table, we want to create a dynamic array showing a unique list of categories where the group is “clothing.” Using the formula =FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing"), we get the following output:

This dynamic array displays a unique list of categories where the group is “clothing,” as required.