You can use the following basic formula to extract unique values based on criteria in Excel:
=UNIQUE(FILTER(B2:B14,A2:A14="West"))
This particular formula finds the unique values in the range B2:B14 where the corresponding cell in the range A2:A14 is equal to “West.”
The following example shows how to use this formula in practice.
Example: Extract Unique Values Based on Criteria in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can type the following formula into cell E1 to extract a list of unique team names where the Conference column is equal to “West”:
=UNIQUE(FILTER(B2:B14,A2:A14="West"))
The following screenshot shows how to use this formula in practice:
We can see that the formula returned the names of the four unique teams who belong to the West conference:
- Lakers
- Mavs
- Spurs
- Rockets
Note: You can find the complete documentation for the Excel FILTER function here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Select a Random Sample in Excel
How to Rank Items by Multiple Criteria in Excel
How to Randomly Select Cells Based on Criteria in Excel