Unique values with criteria

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 D5 is: =UNIQUE(FILTER(B5:B16,C5:C16=E4)) which returns the 5 unique values in group A, as seen in E5:E9.

April 14, 2025 · 1 min · 72 words · Robin Davis

Table of Contents

Explanation

This example uses the UNIQUE function together with the FILTER function.

UNIQUE then removes duplicates, and returns the final array:

UNIQUE and FILTER aredynamic functions.

If data in B5:B16 or C5:C16 changes, the output will update immediately.

Excel formula: Unique values

The output from FILTER is dynamic.

If source data or criteria change, FILTER will return a new set of results.

This makes FILTER a flexible way to isolate and inspect data without altering the…

Excel formula: Distinct values

Excel formula: Unique values with multiple criteria

Excel formula: Unique values ignore blanks

Excel formula: Extract unique items from a list

Excel UNIQUE function

Excel FILTER function