Compare two columns in different worksheets

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a value if criteria are met.

sissey asks:

Array formula in cell N2, worksheet 1:

Back to top

1.1 How to enter an array formula

1. Copy and paste the formula above to cell N2.
2. Press and hold CTRL + SHIFT simultaneously.
3. Press Enter once.
4. Release all keys.

The formula is now surrounded by curly brackets, like this {=formula} if you did it right. Check your formula bar and make sure you have the curly brackets.

Then copy cell N2 and paste to cells below.

Back to top

1.2 Explaining array formula in cell N2

Step 1 - Compare cell F2 (sheet1) with column F (sheet2)

The equal sign is a logical operator that allows you to compare values. It also allows you to compare a value to multiple values, this returns an array of values.

F2=Sheet2!$F$2:$F$8 returns {TRUE; FALSE; FALSE; ...;  FALSE}

TRUE and FALSE are boolean values.

Step 2 - Compare cell G2 (sheet1) with column G (sheet2)

G2=Sheet2!$G$2:$G$8 returns {TRUE; TRUE; TRUE; ... ; FALSE}

Step 3 - Multiply arrays

This step multiples both arrays to apply AND-logic meaning both boolean values must be TRUE in order to return TRUE. See all possible combinations below.

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)

(F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8) returns {1; 0; 0; 0; 0; 0; 0}.

The calculation returns the numerical equivalent to the boolean values, TRUE returns 1, and FALSE returns 0 (zero).

Step 4 - Use IF function to replace calculated values with sheet2 values from column N, if TRUE (1)

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF((F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8), Sheet2!$N$2:$N$8, "") returns {60; ""; ""; ""; ""; ""; ""}.

Step 5 - Calculate the smallest number in the array

The MIN function returns the smallest number in an array or cell range, it ignores logical and text values

MIN(IF((F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8), Sheet2!$N$2:$N$8, ""))

becomes MIN({60;"";"";"";"";"";""}) and returns 60.

Back to top

2. Compare two columns in different worksheets (Excel 2016)

Excel 2016 formula in cell N2:

Back to top

Explaining formula in cell N2

Step 1 - Setup MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria.

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MINIFS(Sheet2!$N$2:$N$8, Sheet2!$F$2:$F$8, F2, Sheet2!$G$2:$G$8, G2)

Step 2 - Evaluate MINIFS function

MINIFS(Sheet2!$N$2:$N$8, Sheet2!$F$2:$F$8, F2, Sheet2!$G$2:$G$8, G2)

becomes MINIFS({60;165;60;250;33;15;16.5},{1;2;4;8;8;28;28},1,{0;0;0;0;2;0;1},0)

and returns 60. The only relative position that meets both criteria is the first one, it contains number 60.

Back to top

Get the Excel file

Compare-two-columns-in-different-worksheetsv3.xlsx

Back to top

This section 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.

4. 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 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)), "") 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)) 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))) returns "E" in cell B11.

Back to top

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

Array Formula in B11:

Array Formula in B15:

Back to top

6. 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:

6.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) 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 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) returns {"E"; "F"}.

Back to top

6.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) 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 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) returns {"G"; "I"; "C"}.

Back to top

7. 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:

7.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) 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 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) 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)) returns {"E"; "F"}.

Back to top

7.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) 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 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) 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)) returns {"C", "G"; "I"}.

Back to top

Get the Excel file

Compare-and-filter-values-in-two-listsv2.xlsx

Back to top

Compare category

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

What values are missing in List 1 that exists i List 2?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

How to highlight differences and common values in lists
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

Excel categories

Comments (1)