How to find unique values Excel

by | Jul 27, 2018 | Excel Tips & Tricks

How to find unique values Excel

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

  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

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.