Select Page

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

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.