How do you find values from different worksheets based on a criteria and return a particular cell value?

Question

I have been given a workbook comprising of three worksheets i.e Sheet1, Sheet2 and Sheet3. My task is to lookup Sheet2 and Sheet3 based on Column A of Sheet1, find a match in Sheet2 and Sheet3 then pupulate Columns D, E, F of Sheet1 with values from Column C in Sheet2 and Sheet3. I hope this is clear enough. Please advise otherwise. Thanks SO My attempt is by using a combination of IF, IFERROR and VLOOKUP fuctions in cells of D E and F of Sheet1 but am getting 0 i.e:

Column D:

=IF(IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0"))=10, IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0")), "0")

Column E:

=IF(IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0"))=12, IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0")), "0")

Column F:

=IF(IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0"))=15, IFERROR(VLOOKUP(A2, Sheet2!$A$2:$D$6, 3,FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$2:$E$6,3,FALSE), "0")), "0")

Sheet2:

Sheet3:

Expected:

Update with help from @basic:

Answer 1

You can try following array formula:

=SUMPRODUCT(IFERROR((CHOOSE({1;2},Sheet2!$A$2:$A$6,Sheet3!$A$2:$A$6)=$A22)*(CHOOSE({1;2},Sheet2!$D$2:$D$6,Sheet3!$D$2:$D$6)=--(RIGHT(C$21,2)))*CHOOSE({1;2},Sheet2!$C$2:$C$6,Sheet3!$C$2:$C$6),0))

Enter it with ctrl + shift + enter then copy right and down.