How to Extract Unique Values Based on Criteria in Excel

To extract unique values based on criteria in Excel, you can use a combination of the FILTER, UNIQUE, and IF functions. The FILTER function will filter the data based on the specified criteria, the UNIQUE function will extract unique values from the filtered data, and the IF function will help to define the criteria.

Here's a step-by-step guide on how to extract unique values based on criteria in Excel:

1. Organize your data in a table, including headers for each column.
2. Identify the column containing the criteria and the column containing the values you want to extract.
3. In a blank cell, use the following formula structure:

=UNIQUE(FILTER(column_with_values, IF(column_with_criteria=criteria, TRUE, FALSE)))

Replace column_with_values with the range of the column containing the values you want to extract, column_with_criteria with the range of the column containing the criteria, and criteria with the specific criteria you're looking for.

Example:

Suppose you have a dataset with Product IDs in column A, Categories in column B, and Prices in column C. You want to extract unique Product IDs based on the criteria that they belong to the "Electronics" category.

1. Organize your data:

A          B             C
---------------------
Product ID Category    Price
1          Electronics 100
2          Clothing    50
3          Electronics 150
4          Clothing    75
5          Electronics 200

2. Identify the columns with the criteria and the values:
   • Column with criteria: B2:B6 (Category)
   • Column with values: A2:A6 (Product ID)
   • Criteria: "Electronics"
3. Enter the formula in a blank cell, such as D2:

=UNIQUE(FILTER(A2:A6, IF(B2:B6="Electronics", TRUE, FALSE)))

4. Press Enter. Excel will display the unique Product IDs that belong to the Electronics category:

D
------
1
3
5

Now you have successfully extracted unique values based on the specified criteria in Excel.