Comparing data between 2 worksheets, mark them as "yes' or "no" based on several condition

Hello guys.

here i got two worksheets. By comparing data between those two worksheets , i want to solve logic function and mark the result with "yes" or "No" along column H (sheet1 -" ). The "yes'" result can be obtained through multiple cases (OR). But in every cases, it must fulfill both condition A and condition B (AND).


So for the example, let say we plug in the formula in H18 :

case1
if column D18 (sheet1) = "Xtpaint" or " paint" or "whtpaint" ,
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) above is equal to A250(sheet2) ,then if B250 = "WDS"
Result - H18="Yes"


case2
if column D18 (Sheet1) = "qc"
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) is equal to A100(sheet2) ,then if B100 = "coating"
Result - H18= "Yes"


case3
if column D18 (Sheet1) = "W/H"
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) is equal to A333(sheet2) ,then if B333 = "Airsheet"
Result - H18= "Yes"

I call sheet1 as "checklist2" and "sheet2 as "submitted"

One guy has gave me this code. it fulfill all the condition at first, and i did a litle modification to suit my needs.

Please Login or Register  to view this content.

It works perfectly until i just found several data has failed above logic condition. You may refer my attachment. I have marked those data that i men, with redhighlight, both in sheet1 and sheet2 just to indicate this code unable to identify them.


defining error.xlsm

I have checked the code for many times, but could not find the error. Really appreaciate if u guys can take a look and detect the error for me.Thanks in advance.