Summary
To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER function. In the example shown, the formula in E5 is:
=UNIQUE(FILTER(B5:B16,C5:C16=E4))
which returns the 5 unique values in group A, as seen in E5:E9. To apply more than one condition, see Multiple criteria below.
Generic formula
=UNIQUE(FILTER(rng1,rng2=A1))
Explanation
In this example, the goal is to extract a list of the unique colors in each group. The colors are in B5:B16, the groups are in C5:C16, and the group to match is in the header above each formula (E4 and G4).
Table of contents
FILTER, then UNIQUE
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to limit data to values associated with group A only:
FILTER(B5:B16,C5:C16=E4)
Notice we are picking up the value "A" directly from the header in cell E4. Inside FILTER, the expression C5:C16=E4 returns an array of TRUE FALSE values like this:
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
This array is used to filter data, and the FILTER function returns the 6 colors in group A:
{"red";"green";"blue";"pink";"gray";"red"}
This array is returned directly to the UNIQUE function as the array argument. UNIQUE then removes duplicates, and returns the final array:
{"red";"green";"blue";"pink";"gray"}
UNIQUE and FILTER are dynamic functions. If data in B5:B16 or C5:C16 changes, the output will update immediately.
The formula in G5, which returns unique values associated with group B, is almost the same:
=UNIQUE(FILTER(B5:B16,C5:C16=G4))
The only difference is that C5:C16 is compared to the value in G4, which is "B".
Multiple criteria
To apply more than one condition, you can use boolean logic inside FILTER. In the worksheet below, the goal is to extract the unique colors in group B with a quantity greater than 5. The formula in F5 is:
=UNIQUE(FILTER(B5:B16,(C5:C16="b")*(D5:D16>5)))
Inside FILTER, the expression used for the include argument is:
(C5:C16="b")*(D5:D16>5)
Each condition returns an array of TRUE and FALSE values. Multiplying the two arrays converts TRUE and FALSE to 1 and 0, and returns 1 only where both conditions are TRUE:
{0;1;0;0;0;1;0;1;0;0;1;1}
Note that the comparison is not case-sensitive, so "b" matches the "B" in column C. FILTER returns the 5 colors in rows with a 1:
{"amber";"purple";"purple";"pink";"pink"}
And UNIQUE removes duplicates and returns the final array:
{"amber";"purple";"pink"}
You can add more conditions the same way, each in its own parentheses. To match any condition instead of all of them (OR logic), add the conditions together instead of multiplying them. For more examples, see Filter with multiple criteria and FILTER with multiple OR criteria.
One extra condition worth knowing: an empty cell in the source data that meets the criteria appears as a zero (0) in the results. To leave empty cells out, add (B5:B16<>"") as another condition, as explained in Unique values ignore blanks.
No matching values
When no data meets the criteria, FILTER returns a #CALC! error, and UNIQUE returns the same error. In the worksheet below, the data has no group C, so the formula in E5 returns #CALC!. To show a message instead, give FILTER a value for its if_empty argument. The formula in G5 is:
=UNIQUE(FILTER(B5:B16,C5:C16=G4,"None"))
FILTER now returns "None" when nothing matches, and UNIQUE returns it as the result. To return nothing at all, use an empty string ("") for if_empty.
Dynamic source range
Because the ranges are hardcoded directly into the formula, they won't resize if data is added or deleted. To use a dynamic range that will automatically resize when needed, you can use an Excel Table, or create a dynamic named range with a formula.
Note: the UNIQUE function is available in Excel 2021+ and Excel 365.
