Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Extract unique values based on one or more criteria in Excel

Author Xiaoyang Last modified
A screenshot of a data range in Excel where unique values will be extracted based on criteria

Extracting unique values based on criteria is a crucial task for data analysis and reporting. Supposing you have the data range on the left, and you want to list only the unique names in column B based on a specific criterion in column A. Whether you're working with older versions of Excel or leveraging the latest features in Excel 365/2021, this guide will show you how to extract unique values efficiently.

Extract unique values based on criteria in Excel

Extract unique values based on multiple criteria in Excel

Extract unique values from a list of cells with Kutools for Excel

Extract unique values based on criteria in Excel

• With array formula to list the unique values vertically

To solve this job, you can apply a complex array formula, please do as follows:

1. Enter the below formula into a blank cell where you want to list the extracting result, in this example, I will put it to cell E2, and then press Shift + Ctrl + Enter keys to get the first unique value.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Then, drag the fill handle down to the cells until blank cells are displayed, and now all the unique values based on the specific criterion have been listed, see screenshot:

A screenshot of unique values extracted in Excel using an array formula based on criteria

Note: In the above formula: B2:B15 is the column range contains the unique values that you want to extract from, A2:A15 is the column contains the criterion you based on, D2 indicates the criterion that you want to list the unique values based on, and E1 is the cell above your entered formula.
 

• Extract and display unique values in a single cell using Kutools for Excel

Kutools for Excel provides a straightforward way to extract unique values and display them in a single cell, saving you time and effort when working with large datasets without remembering any formula.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After installing Kutools for Excel, please do as this:

Click "Kutools" > "Super Lookup" > "One-to-many Lookup(returns multiple results)" to open the dialog box. In the dialog box, please specify the operations as below:

  1. Select the "Output range" and "Lookup values" in the textboxes separately;
  2. Select the table range that you want to use;
  3. Specify the key column and return column from the "Key Column" and "Return Column" drop down separately;
  4. Finally, click the OK button.
    A screenshot of specifying the options in the dialog box

Result:

All unique names based on the criteira are extracted in a single cell, see screenshot:
A screenshot of unique values extracted in a single cell by kutools

 

• With formula in Excel 365, Excel 2021 and later versions to list unique values vertically

With Excel 365 and Excel 2021, functions like UNIQUE and FILTER make extracting unique values more straightforward.

Enter the below formula into a blank cell, and then press Enter key to get all unique names vertically at once.

=UNIQUE(FILTER(B2:B15, A2:A15=D2))

A screenshot of unique values extracted by unique function

Explanation of this formula:
  1. FILTER(B2:B15, A2:A15=D2):
    • FILTER: Filters data from B2:B15.
    • A2:A15=D2: Checks where values in A2:A15 match the value in D2. Only rows that meet this condition are included in the result.
  2. UNIQUE(...):
    Ensures that only unique values from the filtered results are returned.

Extract unique values based on multiple criteria in Excel

• With array formula to list the unique values vertically

If you want to extract the unique values based on two conditions, here is another array formula can do you a favor, please do as this:

1. Enter the below formula into a blank cell where you want to list the unique values, in this example, I will put it to cell G2, and then press Shift + Ctrl + Enter keys to get the first unique value.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Then, drag the fill handle down to the cells until blank cells are displayed, and now all the unique values based on the specific two conditions have been listed, see screenshot:

A screenshot showing unique values in Excel extracted using multiple criteria with an array formula

Note: In the above formula: C2:C15 is the column range contains the unique values that you want to extract from, A2:A15 and E2 are the first range with the criteria that you want to extract unique values based on, B2:B15 and F2 are the second range with the criteria that you want to extract unique values based on, and G1 is the cell above your entered formula.
 

• With in Excel 365, Excel 2021 and later versions to list unique values vertically

For newer Excel versions, extracting unique values based on multiple criteria is much simpler.

Enter the below formula into a blank cell, and then press Enter key to get all unique names vertically at once.

=UNIQUE(FILTER(C2:C15, (A2:A15=E2) * (B2:B15=F2)))

A screenshot of unique values extracted based on more criteira by unique function

Explanation of this formula:
  1. FILTER(C2:C15, (A2:A15=E2) * (B2:B15=F2)):
    • FILTER: Filters data from C2:C15.
    • (A2:A15=E2): Checks if the values in column A match the value in E2.
    • (B2:B15=F2): Checks if the values in column B match the value in F2.
    • *: Combines the two conditions with AND logic, meaning both conditions must be true for a row to be included.
  2. UNIQUE(...):
    Removes duplicate values from the filtered results, ensuring the output contains only unique values.

Extract unique values from a list of cells with Kutools for Excel

Sometimes, you might want to extract unique values from a list of cells. Here, I recommend a useful tool, Kutools for Excel. Its "Extract cells with unique values (include the first duplicate)" utility allows you to quickly extract unique values.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Click a cell where you want to output the result. (Note: Do not select a cell in the first row.)

2. Then click "Kutools" > "Formula Helper" > "Formula Helper", see screenshot:

A screenshot of the Kutools tab in Excel highlighting the Formula Helper option

3. In the "Formulas Helper" dialog box, please do the following operations:

  • Select "Text" option from the "Formula Type" drop down list;
  • Then choose "Extract cells with unique values (include the first duplicate)" from the "Choose a fromula" list box;
  • In the right "Arguments input" section, select a list of cells that you want to extract unique values.

A screenshot of the Formulas Helper dialog in Excel with the Extract cells with unique values option selected

4. Then click Ok button, the first result is displayed into the cell, then select the cell and drag the fill handle over to the cells that you want to list all the unique values until blank cells are shown, see screenshot:

A screenshot showing the output of unique values in Excel using the Kutools Extract cells with unique values feature

Extracting unique values based on criteria in Excel is an essential task for efficient data analysis, and Excel provides multiple ways to achieve this based on your version and needs. By choosing the right method for your version of Excel and your specific requirements, you can efficiently extract unique values. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

More relative articles:

  • Count The Number Of Unique And Distinct Values From A List
  • Supposing, you have a long list of values with some duplicate items, now, you want to count the number of unique values (the values that appear in the list only once ) or distinct values (all different values in the list, it means unique values +1st duplicate values) in a column as left screenshot shown. This article, I will talk about how to deal with this job in Excel.
  • Sum Unique Values Based On Criteria In Excel
  • For example, I have a range of data which contains Name and Order columns, now, to sum only unique values in Order column based on the Name column as following screenshot shown. How to solve this task quickly and easily In Excel?
  • Concatenate Unique Values In Excel
  • If I have a long list of values which populated with some duplicate data, now, I want to find only the unique values and then concatenate them into a single cell. How could I deal with this problem quickly and easily in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in