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

Supposing, you have the following 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 criteria Kutools for Excel**

** 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 this formula: **=INDEX($B$2:$B$17, MATCH(0, IF($D$2=$A$2:$A$17, COUNTIF($E$1:$E1, $B$2:$B$17), ""), 0))** 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, see screenshot:

**Note:** In the above formula: **B2:B17** is the column range contains the unique values that you want to extract from, **A2:A17** 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.

**2**. Then drag the fill handle down to the cells to list all unique values based on the specific criterion, see screenshot:

** Extract unique values based on criteria Kutools for Excel**

If you are not skilled with the complicated formula, here, I will talk about an easy way to solve it without any formulas. You can filter the data by a specific criterion firstly, and then apply the **Select Duplicates & Unique Cells** feature of **Kutools for Excel** to select the unique values and then paste them to other cells you need.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. |

After installing **Kutools for Excel**, please do as follows:(** Download Kutools for Excel Now! ** )

**1**. First, please select the data range that you want to use, and click **Data** > **Filter**, and click the arrow button besides the cell that you want to filter the data based on a specific criterion, in the expanded list box, select the criterion that you want to filter out, see screenshot:

**2**. Then click **OK**, the desired data has been filtered out, and select the values in column B that you want to extract the unique names from, then click **Kutools** > **Select** > **Select Duplicate & Unique Cells**, see screenshot:

**3**. In the **Select Duplicate & Unique Cells** dialog box, select **All unique (Including 1 st duplicates)** under the **Rule** section, see screenshot:

**4**. Then click **Ok** button, all unique values have been selected, and then please copy the values and paste them into the cell that you want to list the result, see screenshot:

** Download and free trial Kutools for Excel Now ! **

** Demo: Extract unique values based on criteria Kutools for Excel**

**Kutools for Excel**: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.** Download and free trial Now! **

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

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