Calculating Commissions for Different Pay Rates

I am trying to calculate commissions per order. I came up with the correct calculation for each level of pay:

Level 1
=IF(BP11=Compensation!$A$3,Compensation!$B$3,0)+IF(BP11=Compensation!$A$2,Compensation!$B$2,0)+IF(BP11=Compensation!$A$4,Compensation!$B$4,0)+IF(AM11=Compensation!$A$5,Compensation!$B$5,0)+IF(AM11=Compensation!$A$6,Compensation!$B$6,0)+IF(AM11=Compensation!$A$7,Compensation!$B$7,0)+IF(AM11=Compensation!$A$8,Compensation!$B$8,0)+IF(AM11=Compensation!$A$9,Compensation!$B$9,0)+IF(AM11=Compensation!$A$10,Compensation!$B$10,0)+IF(BR11=Compensation!$A$11,Compensation!$B$11,0)+IF(BR11=Compensation!$A$12,Compensation!$B$12,0)

Level 2
=IF(BP9=Compensation!$A$3,Compensation!$C$3,0)+IF(BP9=Compensation!$A$2,Compensation!$C$2,0)+IF(BP9=Compensation!$A$4,Compensation!$C$4,0)+IF(AM9=Compensation!$A$5,Compensation!$C$5,0)+IF(AM9=Compensation!$A$6,Compensation!$C$6,0)+IF(AM9=Compensation!$A$7,Compensation!$C$7,0)+IF(AM9=Compensation!$A$8,Compensation!$C$8,0)+IF(AM9=Compensation!$A$9,Compensation!$C$9,0)+IF(AM9=Compensation!$A$10,Compensation!$C$10,0)+IF(BR9=Compensation!$A$11,Compensation!$C$11,0)+IF(BR9=Compensation!$A$12,Compensation!$C$12,0)

Level 3
=IF(BP14=Compensation!$A$3,Compensation!$D$3,0)+IF(BP14=Compensation!$A$2,Compensation!$D$2,0)+IF(BP14=Compensation!$A$4,Compensation!$D$4,0)+IF(AM14=Compensation!$A$5,Compensation!$D$5,0)+IF(AM14=Compensation!$A$6,Compensation!$D$6,0)+IF(AM14=Compensation!$A$7,Compensation!$D$7,0)+IF(AM14=Compensation!$A$8,Compensation!$D$8,0)+IF(AM14=Compensation!$A$9,Compensation!$D$9,0)+IF(AM14=Compensation!$A$10,Compensation!$D$10,0)+IF(BR14=Compensation!$A$11,Compensation!$D$11,0)+IF(BR14=Compensation!$A$12,Compensation!$D$12,0)

What I want to do next is to check the name of the rep against a master list of staff names and level of pay, and then based on their level of pay, calculate the correct commission. I just don't know where to take it from here. Any suggestions?

Is it possible you can post a dummy workbook detailing what you want to do? In many cases, when your formulas look that complicated, there is a more efficient way of doing it.