Managing and organizing data doesn't have to be a daily routine. You can automated tasks like finding the outliers in your data. In this article, we're going to show you how to find unique values Excel.
Syntax
{ =IFERROR(
INDEX( data,
MATCH( 0,
IF( criteria=criteria list,
COUNTIF( mixed reference of on cell above,
data )),0)),"") }
Steps
 Start with IFERROR function to prevent error messages =IFERROR(
 INDEX function will populate the values we need INDEX(
 Enter the data list reference $C$3:$C$11,
 MATCH function will provides the position MATCH(
 Type inn 0 to get unlisted value 0,
 IF function maintains the condition IF(
 Enter criteriacriteria list equation $F$2=$B$3:$B$11,
 Continue with COUNTIF to compare listed values with the data COUNTIF(
 Enter the mixed reference for a range that contains the cell above $H$2:$H4,
 Enter the data list reference again $C$3:$C$11
 Close COUNTIF and IF functions )),
 Type in 0 for an exact match 0
 Close both MATCH and INDEX functions )),
 Add an empty string to get them instead of errors ""
 Close the IFERROR function )
 Press the CTRL + SHIFT + ENTER combination on your keyboard to create this as an array formula
 Copy the formula all the way down
How
We must first find a unique value, that is not listed in any of the previous cells ($H$2:$H2). By searching for a value of 0 in an array that contains 0s, 1s, and FALSE values.
The formula part from H4: IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11)) returns {FALSE;1;FALSE;FALSE;FALSE;0;FALSE;0;FALSE}
While 1 and 0 represent whether the values are listed or not respectively, FALSE values stands for values that don't meet the criteria.
Let's breakdown the formula in H4: =IFERROR(INDEX($C$3:$C$11,MATCH(0,IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11)),0)),"") formula:
Steps  Formula Part  Description  Return Value 
1  COUNTIF($H$2:$H3,$C$3:$C$11)  The COUNTIF returns an array that includes 1 for each existed value of $C$3:$C$11 in expanding $H$2:$H3 and 0 for not existed  {0;1;0;1;0;0;1;0;1} 
2  $F$2=$B$3:$B$11  The IF function's logical test argument generates another array that represents our criteria: $F$2=$B$3:$B$11  {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE} 
3  IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11))  The IF function returns the values of array in step 1 according to its logical test results. If there is a TRUE value at same positon of array, value is listed.  {FALSE;1;FALSE;FALSE;FALSE;0;FALSE;0;FALSE} 
4  MATCH(0,IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11)),0)  The MATCH function searches 0 in array and returns the exact position of matched value  6 
5 
INDEX($C$3:$C$11, MATCH(0,IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11)),0)) 
The INDEX function returns the value in data range by using MATCH function's return value 
"John"

6 
=IFERROR(INDEX($C$3:$C$11, MATCH(0,IF($F$2=$B$3:$B$11,COUNTIF($H$2:$H3,$C$3:$C$11)),0)),"") 
The IFERROR returns the INDEX function's result unless there is an error. 
"John"

Remember to press CTRL + SHIFT + ENTER instead of the ENTER key to define this as an array formula. Otherwise, you will get an #N/A error.