Either:
in cell D2:
=INDEX(Sheet2!C$2:C$11,MATCH($A2 & "¬" & $C2,Sheet2!$A$2:$A$11 & "¬" & Sheet2!$B$2:$B$11,0))
in cell E2:
=INDEX(Sheet2!D$2:D$11,MATCH($A2 & "¬" & $C2,Sheet2!$A$2:$A$11 & "¬" & Sheet2!$B$2:$B$11,0))
both copied down
or if your version of Excel supports the FILTER function, in cell D2:
=FILTER(Sheet2!$C$2:$D$11,(Sheet2!$A$2:$A$11=A2)*( C2=Sheet2!$B$2:$B$11),"Not found")
copied down.