# Matching based on 2 criterias

#### alvbnp

##### Board Regular
I have 2 worksheets for doing matching. The matchings goes like if column A = column A and column B = Column B in both worksheets, then match column E, and highlight those unmatched cell in column E in sheet1. Besides that, also highlight those items which appear in sheet1 but not found in sheet2.

Can this be done just using built in formula in Excel or I need a macro for it?

Thanks for helping.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi

Assuming that the data starts in A1 of both sheets, try the following:

Sheet1!E1: =INDEX(Sheet2!\$E\$1:\$E\$3,SUMPRODUCT(--(Sheet2!\$A\$1:\$A\$3=Sheet1!A1),--(Sheet2!\$B\$1:\$B\$3=Sheet1!B1),ROW(Sheet2!\$E\$1:\$E\$3)),0)

Adjust ranges to suit and copy down as required.

sheet1!A1: Format, Conditional Formatting, formula is: =\$E1=0, select a pattern, and save. Copy format to cells in columns A and B as required.

HTH

Tony

Hi

Assuming that the data starts in A1 of both sheets, try the following:

Sheet1!E1: =INDEX(Sheet2!\$E\$1:\$E\$3,SUMPRODUCT(--(Sheet2!\$A\$1:\$A\$3=Sheet1!A1),--(Sheet2!\$B\$1:\$B\$3=Sheet1!B1),ROW(Sheet2!\$E\$1:\$E\$3)),0)

Adjust ranges to suit and copy down as required.

sheet1!A1: Format, Conditional Formatting, formula is: =\$E1=0, select a pattern, and save. Copy format to cells in columns A and B as required.

HTH

Tony

Sorry, don't really get what you mean. My sheet1!E1 is having data, which I want to match with sheet2!E

Maybe I didn't make it clear enough above. Here again:

Do matching for column E for sheet1 and sheet2 with 2 conditions.
If sheet1:A = sheet2!A and sheet1:B = sheet2!B, then match sheet1:E
Highlight those unmatched items in column E

Data in both sheets are not sorted.

vba;
Code:
``````Sub test()
Dim i As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2").Columns("a")
Set c = .Find(Cells(i, "a").Value, , , xlWhole)
If Not c Is Nothing Then
If c.Offset(, 1) = Cells(i, "b") _
And c.Offset(, 4) <> Cells(i, "e") Then
Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
c.Offset(, 4).Interior.ColorIndex = 6
End If
End If
End With
Next
End Sub``````

Thanks agihcam. It's working. But a little problem here.

Sample data for example:

Sheet1
Column A | B | E
2 | A | 100
2 | B | 1
2 | C | 100

Sheet2
Column A | B | E
2 | B | 100
2 | A | 100
2 | C | 100

The error in column E won't be highlighted on the above sample data, unless the column B is Ascending sorted in both sheets.

Any fixes to overcome this?

Hi -

this should work.
Code:
``````Sub test()
Dim i As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2").Columns("a")
Set C = .Find(Cells(i, "a").Value, , , xlWhole)
If Not C Is Nothing Then
Do
If C.Offset(, 1) = Cells(i, "b") _
And C.Offset(, 4) <> Cells(i, "e") Then
Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
C.Offset(, 4).Interior.ColorIndex = 6
End If
Set C = .FindNext(C)
Loop Until FF = C.Address

End If
End With
Next
End Sub``````

Thanks. It work perfectly.

Need some more help. My cells containing formula, the value of the cells is derived from the formula, and this make the macro stop working.
eg:
=sheet1!A1
=lookup(XXX,YYY)
and so on.

changed from;
Code:
``Set C = .Find(Cells(i, "a").Value, , , xlWhole)``
to
Code:
``Set C = .Find(Cells(i, "a").Value, lookin:=xlvalues)``

Thanks, agihcam. One more thing, is it possible to highlight those missing items, eg: it appear in sheet1 but not in sheet2, just need to highlight those in sheet1.

Replies
6
Views
202
Replies
3
Views
126
Replies
4
Views
462
Replies
5
Views
381
Replies
5
Views
122

1,218,627
Messages
6,143,585
Members
450,494
Latest member
GolfNut39

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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