Author: Oscar Cronquist Article last updated on August 07, 2017

How would you figure out an unique list where the sum of in one column doesn't match the other column? I'm comparing list 1 to list 2
List 1
A 10
A 10
B 5
B 6
B 4
List 2
A 10
A 10
B 5
B 6

Array formula in cell B10:

=INDEX(\$E\$3:\$E\$7, MATCH(0, COUNTIFS(\$B\$3:\$B\$7, \$E\$3:\$E\$7, \$C\$3:\$C\$7, \$F\$3:\$F\$7) + COUNTIF(\$B\$9:B9, \$E\$3:\$E\$7), 0))

### How to enter an array formula

1. Select cell B10
2. Type the above formula
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter once
5. Release all keys

If you did it right, the formula is now encapsulated with curly brackets, like this: {formula}

DonÂ´t enter these characters yourself, they show up automatically.

### Explaining the array formula in cell B10

Step 1 - Compare lists

The COUNTIFS function compares column by column between the two lists. If a record is found 1 or more is returned. Nothing found 0 is returned.

COUNTIFS(\$B\$3:\$B\$7, \$E\$3:\$E\$7, \$C\$3:\$C\$7, \$F\$3:\$F\$7)

returns

{ 1; 1; 1; 1; 0}

Step 2 - Filter only unique distinct values

The COUNTIF function makes sure that only unique distinct values are returned. If a value already exists in the list the function returns 1.

The first argument has a relative and absolute cell referenceÂ \$B\$9:B9. The cell referenceÂ expands as you copy and paste the formula further down.

COUNTIF(\$B\$9:B9, \$E\$3:\$E\$7)

returns

{0; 0; 0; 0; 0}

This means that no valuesÂ have been shown yet.

Step 3 -Â Find the relative position

The MATCH function finds the relative position of the first 0 in the array.

MATCH(0, COUNTIFS(\$B\$3:\$B\$7, \$E\$3:\$E\$7, \$C\$3:\$C\$7, \$F\$3:\$F\$7) + COUNTIF(\$B\$9:B9, \$E\$3:\$E\$7), 0)

becomes

MATCH(0, { 1; 1; 1; 1; 0}Â + {0; 0; 0; 0; 0}, 0)

becomes

MATCH(0,Â { 1; 1; 1; 1; 0} , 0)

and returns 5.

Step 3 - Return values

The INDEX function returns a value in a cell range.

INDEX(\$E\$3:\$E\$7, 5)

becomes

INDEX({"A"; "A"; "B"; "B"; "B"},Â 5)

and returns B in cell B10.