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

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 shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three separate columns

Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

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 to mahmoud-lee

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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