# 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

#### Gogo007

##### New Member
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"))
It's giving an error , "This function isn't valid ", Also I want to do this in VBA. Match Column along with row number in Sheet1 and on sheet2 as well.
Would be great help from till now .
Thank You so much.

#### Attachments

• Annotation 2021-04-14 124720.png
18.4 KB · Views: 4
• sheet2.png
9.4 KB · Views: 4

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

##### Well-known Member
This is VBA Mathod:
VBA Code:
Sub test()
Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1
For j = 2 To Lr2
If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
Sheets("sheet1").Range("D" & i).Value = "Match"
Sheets("sheet1").Range("E" & i).Value = j
GoTo Step2
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Next j
Step2:
Next i
End Sub

#### Gogo007

##### New Member
This is VBA Mathod:
VBA Code:
Sub test()
Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1
For j = 2 To Lr2
If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
Sheets("sheet1").Range("D" & i).Value = "Match"
Sheets("sheet1").Range("E" & i).Value = j
GoTo Step2
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Next j
Step2:
Next i
End Sub
Thank You so much buddy , just 1 more help can we add Row number where its "Match".

##### Well-known Member
I added it to See at Column E at Sheet 1. Are you don't see it?

#### Gogo007

##### New Member

I added it to See at Column E at Sheet 1. Are you don't see it?
Yes, I can see it.
This is the perfect code with Row Number, Thank You so much
Sorry I missed it by mistake.

One more help in this Code
After running this Code,
If the Case is "Unmatched" then I want to Check again if, Column B from sheet1 with Column D from Sheet2 is matching or not and if matched then should Print Match with Row Number, like previous code.

##### Well-known Member
Then how you determine between 2 column match with 4 column match at Result?
If only Column B match with column D, I think it's better to use another criteria.
For e.g. 4 column Match = "Complete Match"
2 Column Match = "Partial Match"
Are you want this?
.

#### Gogo007

##### New Member

Then how you determine between 2 column match with 4 column match at Result?
If only Column B match with column D, I think it's better to use another criteria.
For e.g. 4 column Match = "Complete Match"
2 Column Match = "Partial Match"
Are you want this?
.
Yes We can go with this.
If 4 column match then "Complete Match" with row number
if 2 column match{Column B from sheet1 with Column D from Sheet2 } then "Partial Match " with row number.

##### Well-known Member
Try this:
VBA Code:
Sub test()
Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1
For j = 2 To Lr2
If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
Sheets("sheet1").Range("D" & i).Value = "Complete Match"
Sheets("sheet1").Range("E" & i).Value = j
GoTo Step2
Else
Sheets("sheet1").Range("D" & i).Value = "Partial Match"
Sheets("sheet1").Range("E" & i).Value = j
End If
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Next j
Step2:
Next i
End Sub

#### Gogo007

##### New Member
Try this:
VBA Code:
Sub test()
Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1
For j = 2 To Lr2
If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
Sheets("sheet1").Range("D" & i).Value = "Complete Match"
Sheets("sheet1").Range("E" & i).Value = j
GoTo Step2
Else
Sheets("sheet1").Range("D" & i).Value = "Partial Match"
Sheets("sheet1").Range("E" & i).Value = j
End If
Else
Sheets("sheet1").Range("D" & i).Value = "Not Match"
End If
Next j
Step2:
Next i
End Sub
Hello,
I am finding the below Results

As It's showing the Row Number when Amount is same but Not showing the Title as "Partial Match" .
Otherwise everything is absolutely on Mark.

Thank You.

#### Gogo007

##### New Member
Hello,
I am finding the below Results

View attachment 37854

View attachment 37855
As It's showing the Row Number when Amount is same but Not showing the Title as "Partial Match" .
Otherwise everything is absolutely on Mark.

Thank You.
Basically , Everything is working fine but only "Partial Match" is not printing.

Replies
4
Views
49
Replies
3
Views
65
Replies
2
Views
258
Replies
3
Views
85
Replies
1
Views
118

1,130,252
Messages
5,641,113
Members
417,194
Latest member
Excellent Excel

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