Defining logic "Yes" or "No" , based on multiple conditions

Hello guys.

I have one problem to solve logic function to determine "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 try to use basic function like IF,OR,AND, but it be even complicated. So i guess macro can make it simple, but to be honest i dont know much about macro. Besides, list in Sheet1 and Sheet2 will be constantly updated and added up everyday, so guess the formula should be more flexible about the range. You may refer to my attachment to clearly see how i expect them to be.

Thanks in advance. Appreciate your help.