## Compare two columns and return differences

This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from List 1 that doesn't exist in List 2. The second formula in cell B15 extracts values from List 2 that is not in List 1.

### Table of Contents

## 1. Compare two columns and return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) and not in List 2 (D3:D8). The same formula is used in cell B15, however, with different cell references. This time it extracts values that only exist in List 2 (D3:D8).

Array Formula in B11:

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell D2 and paste it down as far as needed.

Array Formula in B15:

Copy cell D9 and paste it down as far as needed.

### Explaining formula in cell B11

#### Step 1 - Count values in List 1 based on values in List 1

TheÂ COUNTIF functionÂ counts values based on a condition or criteria.

COUNTIF($D$3:$D$8, $B$3:$B$7)=0

becomes

COUNTIF({"D"; "A"; "G"; "Z"; "I"; "C"}, {"A"; "E"; "D"; "Z"; "F"})=0

becomes

{1;0;1;1;0}=0

The logical expression returns TRUE if a value in the array is equal to 0 (zero).

{1;0;1;1;0}=0

returnsÂ {FALSE; TRUE; FALSE; FALSE; TRUE}

#### Step 2 - Replace TRUE with corresponding row number

TheÂ IF functionÂ has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5}, "")

and returns

{""; 2; ""; ""; 5}

#### Step 3 - Extract k-th smallest row number

To be able to return a new value in a cell each I use theÂ SMALL functionÂ to filter row numbers from smallest to largest.

TheÂ ROWS functionÂ keeps track of the numbers based on an expanding cell reference. It will expand automatically when the cell is copied to the cells below.

SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; ""; 5}, ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; ""; 5}, 1)

and returns 2.

#### Step 4 - Get value

TheÂ INDEX functionÂ returns a value based on a cell reference and a row number (also a column number if needed).

INDEX($D$3:$D$8, SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1)))

becomes

INDEX($D$3:$D$8, 2)

and returns "E" in cell B11.

## 2. Compare two columns and return differences sorted from A to Z

Array Formula in B11:

Array Formula in B15:

## 3. Compare two columns and return differences - Excel 365

The formula in cell B11 works only in Excel 365, it contains the new FILTER function. It filters values in list 1 that only exists in list 1 compared to list 2.

Excel 365 dynamic array formula in cell B11:

The formula in cell B15 filters values in list 2 that only exists in list 2 compared to list 1.

Excel 365 dynamic array formula in cell B15:

### 3.1 Explaining formula in cell B11

#### Step 1 - Count values in D3:D8 based on criteria in B3:B7

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF(D3:D8, B3:B7)

becomes

COUNTIF({"D"; "A"; "G"; "Z"; "I"; "C"}, {"A"; "E"; "D"; "Z"; "F"})

and returns

{1; 0; 1; 1; 0}

#### Step 2 - Check if a value in the array is equal to 0 (zero)

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

COUNTIF(D3:D8, B3:B7)=0

becomes

{1; 0; 1; 1; 0}=0

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE}.

#### Step 3 - Filter values not in both lists

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B7, COUNTIF(D3:D8, B3:B7)=0)

becomes

FILTER(B3:B7, {FALSE; TRUE; FALSE; FALSE; TRUE})

becomes

FILTER({"A"; "E"; "D"; "Z"; "F"}, {FALSE; TRUE; FALSE; FALSE; TRUE})

and returns

{"E"; "F"}.

### 3.2 Explaining formula in cell B15

#### Step 1 - Count values in B3:B7 based on criteria inÂ D3:D8

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF(B3:B7, D3:D8)

becomes

COUNTIF({"A"; "E"; "D"; "Z"; "F"}, {"D"; "A"; "G"; "Z"; "I"; "C"})

and returns

{1; 1; 0; 1; 0; 0}

#### Step 2 - Check if a value in the array is equal to 0 (zero)

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

COUNTIF(B3:B7, D3:D8)=0

becomes

{1; 1; 0; 1; 0; 0}=0

and returns

{FALSE; FALSE; TRUE; FALSE; TRUE; TRUE}.

#### Step 3 - Filter values not in both lists

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(D3:D8, COUNTIF(B3:B7, D3:D8)=0)

becomes

FILTER(D3:D8, {FALSE; FALSE; TRUE; FALSE; TRUE; TRUE})

becomes

FILTER({"D"; "A"; "G"; "Z"; "I"; "C"}, {FALSE; FALSE; TRUE; FALSE; TRUE; TRUE})

and returns

{"G"; "I"; "C"}.

## 4. Compare two columns and return differences sorted from A to Z - Excel 365

Excel 365 dynamic array formula in cell B11:

Excel 365 dynamic array formula in cell B15:

### 4.1 Explaining formula in cell B11

#### Step 1 - Count values in D3:D8 based on criteria in B3:B7

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF(D3:D8, B3:B7)

becomes

COUNTIF({"D"; "A"; "G"; "Z"; "I"; "C"}, {"A"; "E"; "D"; "Z"; "F"})

and returns

{1; 0; 1; 1; 0}

#### Step 2 - Check if a value in the array is equal to 0 (zero)

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

COUNTIF(D3:D8, B3:B7)=0

becomes

{1; 0; 1; 1; 0}=0

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE}.

#### Step 3 - Filter values not in both lists

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B7, COUNTIF(D3:D8, B3:B7)=0)

becomes

FILTER(B3:B7, {FALSE; TRUE; FALSE; FALSE; TRUE})

becomes

FILTER({"A"; "E"; "D"; "Z"; "F"}, {FALSE; TRUE; FALSE; FALSE; TRUE})

and returns

{"E"; "F"}.

#### Step 4 - Sort values from A to Z

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(FILTER(B3:B7, COUNTIF(D3:D8, B3:B7)=0))

becomes

SORT({"E"; "F"})

and returns

{"E"; "F"}.

### 4.2 Explaining formula in cell B15

#### Step 1 - Count values in B3:B7 based on criteria inÂ D3:D8

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF(B3:B7, D3:D8)

becomes

COUNTIF({"A"; "E"; "D"; "Z"; "F"}, {"D"; "A"; "G"; "Z"; "I"; "C"})

and returns

{1; 1; 0; 1; 0; 0}

#### Step 2 - Check if a value in the array is equal to 0 (zero)

The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE.

COUNTIF(B3:B7, D3:D8)=0

becomes

{1; 1; 0; 1; 0; 0}=0

and returns

{FALSE; FALSE; TRUE; FALSE; TRUE; TRUE}.

#### Step 3 - Filter values not in both lists

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(D3:D8, COUNTIF(B3:B7, D3:D8)=0)

becomes

FILTER(D3:D8, {FALSE; FALSE; TRUE; FALSE; TRUE; TRUE})

becomes

FILTER({"D"; "A"; "G"; "Z"; "I"; "C"}, {FALSE; FALSE; TRUE; FALSE; TRUE; TRUE})

and returns

{"G"; "I"; "C"}.

#### Step 4 - Sort values from A to Z

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(FILTER(D3:D8, COUNTIF(B3:B7, D3:D8)=0))

becomes

SORT({"G"; "I"; "C"})

and returns

{"C", "G"; "I"}.

### Compare category

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

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 […]

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

### Excel categories

### 5 Responses to “Compare two columns and return differences”

### Leave a Reply

### 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

Oscar here's another variation to this, a much smaller one I guess.

Values in List 1 and not in List 2

=INDEX(List_1, SMALL(IF(ISERROR(1/COUNTIF(List_2, List_1)), ROW(List_1)-MIN(ROW(List_1))+1), ROWS($A$2:A2)))

Chrisham,

Your array formula is great! My array formula creates unique distinct values, that is perhaps not what he asked for.

Thanks a lot for your contribution!!

Guys,

Realy appreciate your contribution.

I need the unique distinct value.

I'll keep both proposed solution. Always interresting to test and compare :-).

Again, thanks a lot.

I have been looking for something similar to this. I am trying to automate a billing process and need some help with it.

Thanks, that was exactly what I needed.