May be this......

On Sheet1, in D2

=IFERROR(INDEX(BC!$B$2:$B$9480,MATCH(B2&C2,INDEX(BC!$C$2:$C$9480&BC!$D$2:$D$9480,0),0)),IF(ISNUMBER(MATCH(B2,BC!$C$2:$C$8286,0)),INDEX(BC!$D$2:$D$8286,MATCH(B2,BC!$C$2:$C$8286,0))&" Not Found","Not Found"))