You can use the following formula to find the Nth unique value in a list in Excel:
=INDEX($A$2:$A$13,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($A$2:$A$13)-ROW($A$2)+1,ROW($A$2:$A$13)-ROW($A$2)+1),3))
This particular formula returns the third unique value in the range A2:A13.
Note that the last 3 in the formula specifies that we’d like to find the third unique value in the range.
To look for a different nth unique value, simply change the 3 to a different number.
The following example shows how to use this formula in practice.
Example: Find Nth Unique Value in Excel
Suppose we have the following dataset that contains information about basketball players on various teams:
Suppose we would like to find the third unique name in the team column.
We can type the following formula into cell D2 to do so:
=INDEX($A$2:$A$13,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($A$2:$A$13)-ROW($A$2)+1,ROW($A$2:$A$13)-ROW($A$2)+1),3))
The following screenshot shows how to use this formula in practice:
The formula returns the team name Spurs, which is the third unique name in the team column.
We can verify that this is correct by using the following formula in cell E2 to list out each unique name in the team column:
=UNIQUE(A2:A13)
This formula returns the following list of unique team names:
From this list we can see that Spurs is indeed the third unique name in the team column.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Unique Names
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria
