Excel: How to Extract Unique Values Based on Criteria


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:

Excel unique values based on criteria

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

Leave a Reply

Your email address will not be published.