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

*Article updated on August 07, 2017*

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.

**Array formula in cell B10:**

### How to enter an array formula

- Select cell B10
- Type the above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- 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

- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Highlight records existing in only one list in excel
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter common row records in excel
- Compare pricelists in excel 2007
- Quickly compare two tables in excel 2007

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

### Download excel *.xlsm

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]### 5 Responses to “Compare two lists and filter unique values where the sum in one column doesn’t match the other column”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

All formulas that belong to you wonderful

Thanks Oscar. Works great. Is there a way to include items that are not in list B in the same formulas. For example C would have avalueof 4 in list A, but it's not in list 2.

Sean,

Thanks Oscar. Works great. Is there a way to include items that are not in list B in the same formulas. For example C would have a value of 4 in list A, but it's not in list 2.Array formula in cell B10:

=IFERROR(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)), INDEX($B$3:$B$7, MATCH(0, COUNTIFS($E$3:$E$7, $B$3:$B$7, $F$3:$F$7, $C$3:$C$7)+COUNTIF($B$9:B9, $B$3:$B$7), 0)))

Thanks so much for this invaluable resource, Oscar! I have a question which I don't think is answered by your posts on this topic. Here's an example:

Apples 1

Apples 1

Oranges 2

Apples 3

Apples 3

Bananas 4

Bananas 4

I'm trying to create a formulate that would generate the following list:

Apples

Oranges

Apples

Bananas

That is, I want the formula to use the second column as a criterion for generating the unique list, but the first column as the range for the corresponding values. I hope this is clear, but I'm happy to elaborate if it isn't. Thanks again!

If you want to know more about "Filter a List into unique records using Countif formula", check this link ........

https://www.exceltip.com/excel-filter/filtering-a-list-into-unique-records-using-the-countif-formula.html