To return distinct values that meet multiple conditions in Excel for Microsoft 365, combine UNIQUE with FILTER. With departments in A2:A100, regions in B2:B100, products in C2:C100, and the requested department and region in E2 and F2, enter =UNIQUE(FILTER(C2:C100,(A2:A100=E2)*(B2:B100=F2),"No matches")). The formula returns each matching product once, in the order it first appears.
What “unique with multiple criteria” means
The question can refer to two different tasks: finding distinct values from one column after applying conditions, or returning distinct records across several columns after applying conditions. The first formula above solves the common case: filter rows by multiple criteria, then list unique values from the result. For example, it can show which products were sold by a selected department in a selected region without repeating a product that appears on several sales rows.
The order of operations matters. FILTER selects rows that pass the conditions; UNIQUE removes repeated values from that filtered result. Using UNIQUE alone would find distinct values in a range but would not apply the criteria. Filtering first ensures that a product appearing elsewhere in the data does not appear unless it also meets the selected conditions.
This is a dynamic-array formula in Microsoft 365 and Excel 2021 or later. Enter it in one cell with clear space below for the results to spill. You do not need to copy it down: Excel returns the list into adjacent cells automatically.
How the formula evaluates multiple conditions
In the example, A2:A100=E2 checks each department against the department criterion, while B2:B100=F2 checks each region against its criterion. Each comparison produces an array of TRUE and FALSE values. Multiplying the two arrays converts TRUE to 1 and FALSE to 0; a row produces 1 only when both comparisons are TRUE. That is why multiplication represents an AND condition.
For instance, with Sales in E2 and East in F2, a row qualifies only if its department is Sales and its region is East. The two criteria ranges and the return range must cover corresponding rows and have the same dimensions. If one range starts at row 2 and another at row 3, Excel cannot reliably match each record to its criteria.
The third argument of FILTER supplies a result when there are no matches. In this example it displays “No matches” instead of a calculation error. That text is then passed through UNIQUE, so the final output is also “No matches.” If you prefer a blank result, replace the text argument with an empty text value: =UNIQUE(FILTER(C2:C100,(A2:A100=E2)*(B2:B100=F2),"")). This still assumes departments in A2:A100, regions in B2:B100, products in C2:C100, and criteria in E2 and F2.
Worked example with product results
Suppose the data has these records: Sales, East, Notebook; Sales, West, Pen; Sales, East, Notebook; Support, East, Headset; and Sales, East, Pen. If E2 contains Sales and F2 contains East, the matching product values are Notebook, Notebook, and Pen. After duplicates are removed, the formula returns Notebook and Pen, each once.
The result follows the first-seen order in the source data. It does not sort the list alphabetically. If the source order changes, the order of unique results can change too. That distinction is useful when the original order carries meaning, such as the order in which products were recorded. If alphabetical order is needed, sort the spilled result separately or use a compatible dynamic-array formula designed to sort it.
Keep the result cell outside the source range when practical. If the output would spill into occupied cells, Excel displays a #SPILL! error. Clear the obstructing cells, move the formula, or make room for the full expected result. A spill formula also cannot expand normally from inside an Excel table; place it in the worksheet grid outside the table.
AND versus OR criteria
Use multiplication when every condition must be true. Use addition when a row may satisfy any one of the conditions. With departments in A2:A100, regions in B2:B100, products in C2:C100, and the criteria in E2 and F2, this version returns products from rows where the department matches E2 or the region matches F2: =UNIQUE(FILTER(C2:C100,(A2:A100=E2)+(B2:B100=F2),"No matches")).
In an OR test, a row that satisfies both conditions produces 2 rather than 1. That is still treated as true by FILTER, so it is included once in the filtered list. UNIQUE then removes repeated product names. For mixed logic, group the tests deliberately. For example, “department is Sales and region is East or West” is not the same condition as “department is Sales or region is East.” Parentheses make the intended grouping easier to read and check.
Multiple AND conditions can be chained with more multiplication terms, such as checks for department, region, and status. Confirm that each comparison returns an array aligned to the same source rows. For several allowed values in one field, consider whether the logic is “any of these values” rather than adding unrelated tests; a longer formula can become difficult to audit, so a helper column or a clearly arranged criteria list may be easier to maintain.
Return unique rows instead of one column
If the goal is to show distinct complete records, pass the full set of columns to FILTER and then to UNIQUE. With department, region, and product data in A2:C100, and the department and region criteria in E2 and F2, use =UNIQUE(FILTER(A2:C100,(A2:A100=E2)*(B2:B100=F2),"No matches")).
This returns distinct rows from the three-column range for records meeting both conditions. It removes a row only when the entire returned row is a duplicate. Two Sales, East records with different products remain separate; two records identical across all three selected columns collapse into one. If you include an ID or timestamp column that differs on every record, otherwise similar rows will count as distinct. Choose the output columns based on what “duplicate” means for your task.
For a unique list of customers based on several conditions, return the customer column rather than the entire record. For a deduplicated report of department, region, and product combinations, return all three columns. The criteria columns do not have to be included in the output, though they often are useful for checking why a row qualified.
Exact matches, blanks, and data quality
Equality checks such as A2:A100=E2 test for an exact value match, not a partial word match. Text comparisons in standard Excel formulas are generally not case-sensitive, so Sales and SALES match. If case must distinguish records, a different, explicit case-sensitive comparison is needed. Also check for leading or trailing spaces: “East” and “East ” can behave as different text values, even if they look alike on screen.
Blank cells can also create surprising results. If a criterion cell is blank, comparing a source range with it can match blank entries. If blank criteria should mean “ignore this condition,” the formula needs to be designed for optional criteria rather than simply multiplying every test. Decide what an empty criterion should mean before relying on the output.
Data types matter as well. A number stored as text may not compare as expected with a true number, and dates can be stored as text rather than Excel date values. If results are missing, inspect representative cells, remove unwanted spaces, and make sure matching values use consistent types. Avoid fixing apparent mismatches by changing the formula until you have verified that the source data represents the same values.
Troubleshooting and formula limits
#NAME?: The Excel version may not supportUNIQUEorFILTER, or a function name may be mistyped. Both functions are available in Microsoft 365 and Excel 2021 or later.#SPILL!: One or more cells in the intended output area are not empty, or the formula is in a table that cannot accommodate a spilled result. Clear the spill range or move the formula outside the table.- An unexpected blank or “No matches”: Check the criteria cells, spelling, spaces, data types, and alignment of the source ranges. Confirm that the selected conditions genuinely match at least one row.
- Too many results: Verify whether the criteria should be AND or OR. Addition accepts rows meeting either condition; multiplication requires all conditions to be true.
- Repeated-looking results: Values may differ by spaces, hidden characters, or other underlying content. If returning rows, a difference in any included column makes those rows distinct.
For a large worksheet, use bounded ranges or an Excel table rather than entire columns if calculation speed becomes an issue. A table can expand as new data is added, but the spill formula itself should sit outside the table. When rows are added, check that the formula’s source references include them; fixed ranges such as row 2 through row 100 will not automatically cover row 101.
Version support and alternatives
UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later, including the dynamic-array behavior needed for a spilling list. The same formula logic works in Excel for Windows, Mac, and the web where these functions are supported. On systems with regional settings that use semicolons as argument separators, Excel may display or require semicolons instead of commas.
In older Excel versions without these functions, there is no equally simple single-cell dynamic-array equivalent. One option is Data > Sort & Filter > Advanced, which can filter a list and copy unique records to another location; arrange the criteria range correctly and review the Advanced Filter options. Power Query is another practical choice for repeatable data-cleaning work: load the data, filter the relevant columns, and remove duplicates from the columns that define uniqueness. A PivotTable can summarize distinct combinations in some workflows, but it is not a direct replacement for a formula that returns a live list of matching values.
For a one-time cleanup, Excel’s Remove Duplicates command can remove repeated records, but it changes the selected data and is not a live formula result. Save a copy or work on a separate range if preserving the original is important. Choose a formula when the list should update as criteria or source data change; choose a query or manual tool when a refreshable or one-off data-preparation process fits better.
Frequently Asked Questions
Can I use UNIQUE with two or more criteria?
Yes. Filter the return range using all the criteria, then wrap that filtered result in UNIQUE. Multiply comparison arrays for AND logic; use addition when matching any condition should qualify.
Does UNIQUE remove duplicates before or after filtering?
In the recommended pattern, FILTER runs first and returns qualifying values or rows. UNIQUE then removes duplicates from that filtered result, so unrelated rows do not affect the output.
How do I return unique rows that meet criteria in two columns?
Use the full output range as the first argument to FILTER, and use the criteria-column comparisons as its include argument. Wrapping the result in UNIQUE removes identical complete rows, not rows that merely share one field.
Why does my formula show a spill error?
Excel needs empty cells in the area where the result will appear. Clear any cells blocking the output or move the formula to a larger open area. A dynamic array cannot spill from within an Excel table.
Can I use this formula in Excel 2019?
Excel 2019 does not include the UNIQUE and FILTER dynamic-array functions. Use an alternative such as Advanced Filter, Power Query, or a PivotTable, depending on whether you need a one-time result or a process that can be refreshed.
Related
- Record Data in Excel Online: Setup, Sharing and Tips
- Gujarati Data in Excel: Typing, Sorting, and Importing
- Excel Data Analysis: How to Find Useful Insights
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation Formula: Rules, Examples & FixesAn Excel data validation formula is a custom rule that controls what users can enter in selected cells.…
- Excel Sum by Month: Formulas, Dates, and PivotTablesTo sum values by month in Excel, use SUMIFS with a start date for the month and an…
- Excel COUNTIF and SUMIF Examples That Actually WorkIn Excel, use COUNTIF(range, criteria) to count cells that meet one condition and SUMIF(range, criteria, sum_range) to add…
- SUMIF in Excel: Count Values or Sum Them CorrectlySUMIF adds numbers that meet a condition; it does not count matching cells. To count matches, use COUNTIF…
- How to Find the Mode in Excel: Formulas and ExamplesTo find the most frequently occurring number in Excel, enter =MODE.SNGL(A2:A11) in an empty cell, replacing A2:A11 with…
- Rows in Excel Nederlands: Meaning and the RIJEN FunctionIn Dutch, Excel rows are called rijen; the English Excel function ROWS is named RIJEN in Dutch-language Excel.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.