How to Combine the FILTER and UNIQUE Functions in Google Sheets to Extract Distinct Filtered Lists

When analyzing customer transactions, event attendance sheets, or product sales logs in Google Sheets, analysts frequently need to isolate records matching specific conditions (such as sales in a particular region or active subscription tiers). However, using the standard FILTER function alone often outputs dozens of repetitive rows because individual customers purchase multiple times. Manually copying, pasting, and running deduplication tools is tedious and breaks formula automation. By nesting FILTER inside the UNIQUE function, you can dynamically extract clean, deduplicated datasets in a single spill array.

How Nesting UNIQUE and FILTER Works

The calculation sequence evaluates from the inside out:

1. The FILTER Function (Inner Layer): Scans the target dataset and extracts only the rows that satisfy your specified logical condition(s).
2. The UNIQUE Function (Outer Layer): Takes the resulting filtered array and strips out all duplicate entries, returning only distinct values or unique rows.

Because both functions are dynamic array formulas, the final list updates instantly whenever source data is added, edited, or removed.

Formula Syntax and Mathematical Structure

The standard syntax for combining these functions is:

=UNIQUE(FILTER(range, condition1, [condition2, ...]))

• range: The column or table containing the data you want to extract (e.g. A2:A100).
• condition1: A Boolean test of matching dimensions that evaluates to TRUE or FALSE (e.g. B2:B100 = "Completed").

Step-by-Step Practical Examples

1. Extracting a Distinct List of Customers from a Completed Region

Suppose column A contains customer names, column B contains sales regions, and column C contains order status. To extract a deduplicated list of customers who made purchases in the “Europe” region:

=UNIQUE(FILTER(A2:A100, B2:B100 = "Europe"))

Google Sheets evaluates all transactions in Europe, discards customer name repeats, and spills a clean list of unique customer names starting at the formula cell.

2. Filtering by Multiple Conditions (AND Logic)

To extract distinct customers who purchased in “Europe” AND whose order status is “Shipped”:

=UNIQUE(FILTER(A2:A100, B2:B100 = "Europe", C2:C100 = "Shipped"))

3. Handling Datasets with No Matches (#N/A Error Handling)

If no rows meet your filtering criteria, the inner FILTER function returns a #N/A error. Wrap the entire formula inside IFNA or IFERROR to display a friendly fallback message:

=IFNA(UNIQUE(FILTER(A2:A100, B2:B100 = "Europe")), "No matching records found")

4. Alphabetically Sorting the Deduplicated List

To sort your unique filtered list alphabetically from A to Z, wrap the expression with the SORT function:

=SORT(UNIQUE(FILTER(A2:A100, B2:B100 = "Europe")), 1, TRUE)

Filtering and Deduplicating Entire Multi-Column Tables

You can also deduplicate across multiple columns simultaneously:

=UNIQUE(FILTER(A2:C100, C2:C100 = "Completed"))

In this configuration, UNIQUE evaluates rows across columns A, B, and C as a composite unit. It removes a row only if all three column values are identical duplicates of an earlier row, preserving records with different regional or transaction attributes.