Try the following:
In Sheet 3, add "Discount" and "Discount Amount" in columns E & F
in E2:
=INDEX(Sheet2!$A$2:$D$7,MATCH(B2,Sheet2!$A$2:$A$7,0),MATCH(VLOOKUP(A2,Sheet1!$A$2:$B$5,2,FALSE),Sheet2!$A$1:$D$1,0))
in F2
=D2*E2
(THese could be combined into a single column)
in Sheet4 :
in C2:
=SUMIF(Sheet3!$A$2:$A$8,Sheet4!A2,Sheet3!$F$2:$F$8)
Copy all formulas down as required.
HTH
