# Compare 2 columns from sheet 1 with 2 columns in sheet 2 in single workbook

#### Gogo007

##### New Member
I have single workbook having 2 sheets(sheet1,sheet2).
I wanted to compare 2 columns(A,B) value from sheet1 with 2 columns(C,D), if they match then in in next column of sheet 1 cell value should be "Match" and if not then "Not match")
In sheet1(A column should equal to ==)sheet2(C column)
In sheet1(B column should equal to ==)sheet2(D column)

e.g 1
SHEET1 SHEET2
A B C D E
101 2000 101 2000 Match (All 4 values are same)

e.g 2
SHEET1 SHEET2
A B C D E
101 2000 201 2000 Not Match (A and C not equal)

e.g 3
SHEET1 SHEET2
A B C D E
101 2000 101 3000 Not Match (B and D are not equal)

e.g 4
SHEET1 SHEET2
A B C D E
301 7000 101 3000 Not Match (A and B not equal as well as B and D are not equal)

please if anyone can help me in this

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

##### Well-known Member
Try this:
Sheet1:
Cell Formulas
RangeFormula
E2:E6E2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match(All 4 Values Same)",IF((Sheet1!A2<>Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Not Match(A & C Not Equal)",IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2<>Sheet2!D2),"Not Match(B & D Not Equal)","Not Match(A and B not equal as well as B and D are not equal)")))
F2:F6F2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")

Sheet2:
Book1.xlsx
ABCDE
1
21011000
32012000
43013000
54014000
65015000
7
Sheet2

##### Well-known Member
Hi
VBA?
VBA Code:
``````Sub test()
Dim a As Variant, i
a = Sheets("sheet1").Range("a2").CurrentRegion
With CreateObject("scripting.dictionary")
For i = 1 To UBound(a)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1) & a(i, 2)) Then
.Add (a(i, 1) & a(i, 2)), ""
End If
End If
Next
a = Sheets("sheet2").Range("d2").CurrentRegion
For i = 1 To UBound(a)
If Not .exists(a(i, 1) & a(i, 2)) Then
a(i, 1) = "Not Match"
Else
a(i, 1) = "Match"
End If
Next
Sheets("Sheet1").Cells(2, 3).Resize(UBound(a)) = a
End With
End Sub``````

##### Well-known Member
Hi @mohadin . I think this Line
VBA Code:
``a = Sheets("sheet2").Range("d2").CurrentRegion``
Should be:
VBA Code:
``a = Sheets("sheet2").Range("c2").CurrentRegion``

Yes in deed.
Thank you

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff 1.xlsm
ABCD
1
21011000
32012000
43013000
54014000
65015000
7
8
Sheet2

+Fluff 1.xlsm
ABC
1
21011000Match
33013000Match
44013000No Match
53015000No Match
65015000Match
7
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IF(COUNT(FILTER(ROW(Sheet2!\$C\$2:\$D\$10),(Sheet2!\$C\$2:\$C\$10=A2)*(Sheet2!\$D\$2:\$D\$10=B2))),"Match","No Match")

##### Well-known Member

VBA Code:
``````Sub test()
Dim a As Variant, i
a = Sheets("sheet1").Range("A2").CurrentRegion
With CreateObject("scripting.dictionary")
For i = 1 To UBound(a)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1) & a(i, 2)) Then
.Add (a(i, 1) & a(i, 2)), ""
End If
End If
Next
a = Sheets("sheet2").Range("C2").CurrentRegion
For i = 1 To UBound(a)
If Not .exists(a(i, 1) & a(i, 2)) Then
a(i, 1) = "Not Match"
Else
a(i, 1) = "Match"
End If
Next
Sheets("Sheet1").Cells(2, 3).Resize(UBound(a)) = a
End With
End Sub``````

#### Gogo007

##### New Member
T
Try this:
Sheet1:
Cell Formulas
RangeFormula
E2:E6E2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match(All 4 Values Same)",IF((Sheet1!A2<>Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Not Match(A & C Not Equal)",IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2<>Sheet2!D2),"Not Match(B & D Not Equal)","Not Match(A and B not equal as well as B and D are not equal)")))
F2:F6F2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")

Sheet2:
Book1.xlsx
ABCDE
1
21011000
32012000
43013000
54014000
65015000
7
Sheet2
Thanks a lot buddy ,
Can we find out the row no when its a MATCH

#### Fluff

##### MrExcel MVP, Moderator
Book1
ABC
1
21011000Match row 2
33013000Match row 4
44013000No Match
53015000No Match
65015000Match row 6
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=LET(Rws,FILTER(ROW(Sheet2!\$C\$2:\$D\$10),(Sheet2!\$C\$2:\$C\$10=A2)*(Sheet2!\$D\$2:\$D\$10=B2)), IF(COUNT(Rws),"Match row "&Rws,"No Match"))

#### Gogo007

##### New Member
Hello I am trying below code and giving me error on : "Match" i.e Expected end of statement
Worksheets("MOVEX").Range("X2").Formula = "=IF((MOVEX!E2=Bank!J2)*(MOVEX!P2=Bank!G2), "Match","Not Match")"
Lrs = Worksheets("MOVEX").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("MOVEX").Range("X2:X" & Lrs).FillDown

Replies
4
Views
44
Replies
3
Views
65
Replies
2
Views
245
Replies
3
Views
74
Replies
1
Views
113

1,129,902
Messages
5,638,896
Members
417,058
Latest member
BRYCEPIETROWIAK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back