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.



  INDEX( data,

    MATCH( 0,

     IF( criteria=criteria list,

        COUNTIF( mixed reference of on cell above,

        data )),0)),””) }


  1. Start with IFERROR function to prevent error messages =IFERROR(
  2. INDEX function will populate the values we need INDEX(
  3. Enter the data list reference $C$3:$C$11,
  4. MATCH function will provides the position MATCH(
  5. Type inn 0 to get unlisted value 0,
  6. IF function maintains the condition IF(
  7. Enter criteria-criteria list equation $F$2=$B$3:$B$11,
  8. Continue with COUNTIF to compare listed values with the data COUNTIF(
  9. Enter the mixed reference for a range that contains the cell above $H$2:$H4,
  10. Enter the data list reference again $C$3:$C$11
  11. Close COUNTIF and IF functions )),
  12. Type in 0 for an exact match 0
  13. Close both MATCH and INDEX functions )),
  14. Add an empty string to get them instead of errors “”
  15. Close the IFERROR function )
  16. Press the CTRL + SHIFT + ENTER combination on your keyboard to create this as an array formula
  17. Copy the formula all the way down


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,


The INDEX function returns the value in data range by using MATCH function’s return value “John”


6 =IFERROR(INDEX($C$3:$C$11,


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.