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

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles