## How to extract unique values based on criteria in Excel?

Supposing, you have the left data range that you want to list only the unique names of column B based on a specific criterion of column A to get the result as below screenshot shown. How could you deal with this task in Excel quickly and easily?

**Extract unique values based on criteria with array formula**

**Extract unique values based on multiple criteria with array formula**

**Extract unique values from a list of cells with a useful feature**

** Extract unique values based on criteria with array formula**

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:

**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 unique values based on multiple criteria with array formula**

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:

**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.

** Extract unique values from a list of cells with a useful feature**

Sometimes, you just want to extract the unique values from a list of cells, here, I will recommend a useful tool-**Kutools for Excel**, with its **Extract cells with unique values (include the first duplicate)** utility, you can quickly extract the unique values.

**Note:**To apply this

**Extract cells with unique values (include the first duplicate)**, firstly, you should download the

**Kutools for Excel**, and then apply the feature quickly and easily.

After installing **Kutools for Excel**, please do as this:

**1**. Click a cell where you want to output the result. (* Note:Don't click a cell in the first row.*)

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

**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.

**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:

** Free Download Kutools for Excel Now!**

** 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?

**Transpose Cells In One Column Based On Unique Values In Another Column**- Supposing, you have a range of data which contains two columns, now, you want to transpose cells in one column to horizontal rows based on unique values in another column to get the following result. Do you have any good ideas to solve this problem 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

**Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel**

**Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...**

Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.

#### 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!