Author: Oscar Cronquist Article last updated on November 27, 2017

sissey asks:

Hi Oscar,
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
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!

compare two columns in different worksheets

Array formula in cell N2, worksheet 1:

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

How to enter an array formula

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

Explaining array formula

The steps below is for cell N2 formula.

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




and returns


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




and returns


Step 3 - Multiply arrays




and returns


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


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


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

and returns


Step 5 - MIN function finds the number in the array, ignores logical and text values




and returns 60.

Download excel *.xlsx file

Compare two columns in different worksheets.xlsx