Hope this will help you.

in sheet 2, Copy below formula to column g7 to g28

=IF(ISNA(MATCH(E7,Sheet3!$F$6:$F$14,0)),IF(ISNA(MA TCH(E7,Sheet3!$G$6:$G$14,0)),IF(ISNA(MATCH(E7,Shee t3!$H$6:$H$14,0)),"","Lot 3"),"Lot 2"),"Lot 1")

in sheet 1, copy below formula to column g8 to o10

=SUMIFS(Sheet2!$F$7:$F$28,Sheet2!$D$7:$D$28,Sheet1 !G$7,Sheet2!$G$7:$G$28,Sheet1!$F8)