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

**What's on this page**

There are multiple columns in two different worksheets, one has more columns than another. I need to compare column F of worksheet 1 and column E of worksheet 2; if the value matches, compare column G of worksheet 1 and column F of worksheet 2; if the value matches, record the value in column N of worksheet1 from column M of worksheet 2. Please see the example below.

Worksheet 1:

Column F Column G Column N

Item Code Item Sub-Code Bank Fee

1 0

2 0

4 0

8 0

28 0

Worksheet2:

Column E Column F Column M

Item Code Item Sub-Code Bank Fee

1 0 60

2 0 165

4 0 60

8 0 250

8 2 33

28 0 15

28 1 16.5

Appreciate your help in advance!

Array formula in cell N2, worksheet 1:

## 1.1 How to enter an array formula

- Copy and paste the formula above to cell N2.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- 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.

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

becomes

1={1;2;4;8;8;28;28}

and returns

{TRUE; FALSE; FALSE; FALSE; 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

becomes

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

and returns

{TRUE; TRUE; TRUE; TRUE; FALSE; 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)

becomes

({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}) * ({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE})

and 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, "")

becomes

IF({1;0;0;0;0;0;0}, Sheet2!$N$2:$N$8, "")

becomes

IF({1; 0; 0; 0; 0; 0; 0}, {60; 165; 60; 250; 33; 15; 16.5}, "")

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

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

Excel 2016 formula in cell N2:

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

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

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

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

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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