To extract unique values in Excel with criteria, combine the FILTER and UNIQUE functions so the results include only records that meet your conditions.
The examples below show how to handle one or multiple criteria, sort the results, and work around older Excel versions.
Extract unique values that match one condition
Suppose a worksheet has product sales in columns A and B:
- Column A: salesperson, in A2:A10
- Column B: product, in B2:B10
To list each distinct product sold by Ana, enter this formula in an empty cell such as D2:
=UNIQUE(FILTER(B2:B10,A2:A10="Ana",""))
FILTER first returns the products from B2:B10 whose corresponding salesperson in A2:A10 is Ana.
UNIQUE then removes repeated products from that filtered list.
The results spill into cells below D2 automatically.
For example, if the matching rows contain Pen, Notebook, Pen, and Folder, the result is Pen, Notebook, and Folder.
The order follows the first occurrence in the source data.
Use a cell as the criterion
Hard-coding a criterion in a formula works, but a cell reference is easier to change.
If F1 contains the salesperson’s name, use:
=UNIQUE(FILTER(B2:B10,A2:A10=F1,""))
Changing F1 from Ana to Luis updates the results without editing the formula.
This is useful for report selectors and reusable templates.
Apply multiple criteria
To return unique products for Ana in the East region, assume column C contains region names.
Use multiplication to combine conditions, which applies AND logic:
=UNIQUE(FILTER(B2:B10,(A2:A10="Ana")*(C2:C10="East"),""))
Both tests must be true for a row to be included.
To use values in cells F1 and G1 instead, write:
=UNIQUE(FILTER(B2:B10,(A2:A10=F1)*(C2:C10=G1),""))
For OR logic, add the conditions instead.
This returns a product if either the salesperson is Ana or the region is East:
=UNIQUE(FILTER(B2:B10,(A2:A10="Ana")+(C2:C10="East"),""))
In these formulas, the source, criteria ranges, and criteria values must line up row by row.
For instance, B2:B10, A2:A10, and C2:C10 all contain nine rows.
Return unique values in alphabetical order
UNIQUE preserves the source order by default.
Wrap it in SORT to alphabetize the filtered results:
=SORT(UNIQUE(FILTER(B2:B10,A2:A10="Ana","")))
To sort in descending order, add -1 as SORT’s sort order argument:
=SORT(UNIQUE(FILTER(B2:B10,A2:A10="Ana","")),1,-1)
The first argument is the array to sort, 1 selects its first column, and -1 requests descending order.
Exclude blank cells
If the product range contains blanks, add a not-blank condition so an empty result is not included among the unique values:
=UNIQUE(FILTER(B2:B10,(A2:A10="Ana")*(B2:B10<>""),""))
Here, <> is Excel’s not-equal-to operator.
In HTML, it must be escaped in code text as shown; enter the ordinary operator in Excel itself.
Find distinct values that occur exactly once
UNIQUE has a second argument that changes its behavior.
Set it to TRUE to return only values that appear exactly once in the filtered results:
=UNIQUE(FILTER(B2:B10,A2:A10="Ana",""),,TRUE)
The omitted second argument keeps UNIQUE’s default comparison by column.
The third argument requests values that occur once, rather than values that merely appear once in the output list.
For example, if Ana’s filtered products are Pen, Pen, and Folder, only Folder is returned.
Use Excel Tables for expanding data
Fixed ranges such as A2:A10 do not automatically include rows added below row 10.
If the data is in an Excel Table named Sales with columns Salesperson, Product, and Region, use structured references:
=UNIQUE(FILTER(Sales[Product],Sales[Salesperson]=F1,""))
Table references expand when you add rows, which reduces the chance of leaving new records out of a report.
Create or rename a table from the Table Design tab.
Which Excel versions support these formulas?
UNIQUE and FILTER are dynamic-array functions available in Microsoft 365 and Excel 2021 or later, including supported editions of Excel for the web.
Excel 2019 and earlier generally do not include these functions.
A formula using them may show #NAME? in an unsupported version.
Google Sheets also has UNIQUE and FILTER functions, but its syntax and behavior can differ in some cases.
Check the function documentation for the spreadsheet app and version you are using before moving a formula between applications.
Options for older Excel versions
In Excel 2019 or earlier, use the Advanced Filter command to copy distinct records that meet criteria.
Set up a criteria area with the field heading and desired condition, select the source data, and choose Data > Advanced.
Select “Copy to another location,” specify the criteria range and destination, then enable “Unique records only.” This is a manual refresh workflow rather than a formula that spills updated results.
For a formula-based workflow in older versions, a helper column with COUNTIF or COUNTIFS can mark the first occurrence of each qualifying value.
You can then extract marked values using an array formula.
These formulas are more complex, and some older Excel versions require Ctrl+Shift+Enter to confirm array formulas.
Power Query is another option for repeatable filtering and deduplication through Excel’s Get & Transform tools.
Troubleshoot common problems
- #SPILL!: Clear any nonempty cells in the area where the results need to spill.
A merged cell or a formula inside an Excel Table’s data area can also prevent a dynamic array from expanding. - No matching results: FILTER returns the optional if_empty result, which is an empty string in the examples; depending on the surrounding formula, the output may appear as a blank result rather than a truly empty spill range.
Check spelling, extra spaces, and whether numbers are stored as text. - Unexpected duplicates: UNIQUE compares underlying values.
Text that looks the same can differ because of spaces or other characters.
TRIM can remove ordinary leading and trailing spaces, but avoid changing source values without checking whether the differences are meaningful. - Range-size errors: Make sure the values range and every criteria range have the same number of rows.
- Formula separators: Depending on regional settings, Excel may expect semicolons instead of commas between function arguments.
Replace the separators if Excel reports a formula syntax error.