## Compare two columns in different worksheets

*Article last updated on November 27, 2017*

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

### How to enter an array formula

- Copy and paste the formula above in 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.

### Explaining array formula

The steps below is for cell N2 formula.

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

F2=Sheet2!$F$2:$F$8

becomes

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

and returns

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

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

(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}

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

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 - MIN function finds the number in the array, 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.

### Download excel *.xlsx file

Compare two columns for same values [Excel Formula]

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

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Filter common values from three columns

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]

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

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

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

Filter values in common between two cell ranges [UDF]

I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form