Conditional formatting based on matching values of multiple cells on another sheet

[chrisneu]

Hello, Forum! I have the following project: I have two sheets – Sheet1 and Sheet2. I need the value(s) of column i of Sheet1 turn green if they match with the value(s) of column J on Sheet2 of the same brand (column A) and model (column B) on both sheets. For example: Sheet1!A4 = Brand1; Sheet1!B4 = Model1; Sheet2!A12 = Brand1; Sheet1!B12 = Model1; Sheet2!J122 = SampleValue1; entering "SampleValue2" string into cell i4 of Sheet1 will retain default color, but entering "SampleValue1" into i4 should change its font color to green, because it matches its string with the string in Sheet2!J122 of the same Brand1 and Model1.

I hope my description is not too confusing. What I have right now is I mirrored the column J of Sheet2 on Sheet1 by creating column H. In other words – column H on Sheet1 is a copy of column J on Sheet2. Then I applied conditional formatting to the column I on Sheet1 with the following formula: =MATCH(i4,$H:$H,0) and applied to =$i$4:$i$5000. It mostly works perfectly, the way I wanted it, but for one problem: for some unknown to me reason, some values in column i are green where there is no value in the same row in mirror column H. I think, what it does, it matches the value of the cell of column i with the entire column H, instead of looking in the corresponding row only.

I don’t need to stick to my particular formula. I will be just as happy if someone can suggest any other working solution. Thanks in advance!

[JBeaucaire]

Did you forget a sample workbook displaying these scenarios? Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook with good sample data and manually colored examples.

[chrisneu]

It appears that I solved my own problem: I changed =MATCH(i4,$H:$H,0) to =MATCH(i4,$H4,0) and now it appears to work as intended.