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

Sean asks:

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
Answer is B.

Compare two lists and filter unique values where the sum in one column doesn't match the other column

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.

Recommended reading

If you want to learn more about array formulas join Advanced excel course.

Download excel *.xlsm

Compare two lists and filter unique values where the sum in one column doesn’t match the other column.xlsx

Related articles