commision payout

[sachin483]

I have this grid for commission payout arrived at this formula,but if the ach% is >120 it should pay 120% amount now reflecting zero can we modify this formula ie:- grid can be of >120 to 150 but after formula checks at end if no amount is there of that ach% it should pay the last max amount and so on

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl64, width: 64"]DIV[/TD]
[TD="class: xl63, width: 64"]ACH[/TD]
[TD="class: xl63, width: 64"]A[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]16876[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]101[/TD]
[TD="class: xl65"]17976[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]102[/TD]
[TD="class: xl65"]19076[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]103[/TD]
[TD="class: xl65"]20176[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]104[/TD]
[TD="class: xl65"]21276[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]105[/TD]
[TD="class: xl65"]22376[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]106[/TD]
[TD="class: xl65"]25996[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]107[/TD]
[TD="class: xl65"]27516[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]108[/TD]
[TD="class: xl65"]29036[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]109[/TD]
[TD="class: xl65"]30556[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]110[/TD]
[TD="class: xl65"]32076[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]111[/TD]
[TD="class: xl65"]34861[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]112[/TD]
[TD="class: xl65"]36496[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]113[/TD]
[TD="class: xl65"]38131[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]114[/TD]
[TD="class: xl65"]39766[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]115[/TD]
[TD="class: xl65"]41401[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]116[/TD]
[TD="class: xl65"]44716[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]117[/TD]
[TD="class: xl65"]46456[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]118[/TD]
[TD="class: xl65"]48196[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]119[/TD]
[TD="class: xl65"]49936[/TD]
[/TR]
[TR]
[TD="class: xl64"]ZZ[/TD]
[TD="class: xl65"]120[/TD]
[TD="class: xl65"]51676[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl66, width: 64"]DD[/TD]
[TD="class: xl68, width: 64"]WW[/TD]
[TD="class: xl68, width: 64"]ACH[/TD]
[TD="class: xl68, width: 64"]PAY[/TD]
[TD="class: xl68, width: 64"]AA2[/TD]
[TD="class: xl68, width: 64"]I2[/TD]
[/TR]
[TR]
[TD="class: xl66"]ZZ[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl65"]106[/TD]
[TD="class: xl67"]25996[/TD]
[TD="class: xl67"]85[/TD]
[TD="class: xl67"]95[/TD]
[/TR]
[TR]
[TD="class: xl66"]ZZ[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl65"]120[/TD]
[TD="class: xl67"]51676[/TD]
[TD="class: xl67"]85[/TD]
[TD="class: xl67"]95[/TD]
[/TR]
[TR]
[TD="class: xl66"]ZZ[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl65"]125[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]85[/TD]
[TD="class: xl67"]95[/TD]
[/TR]
</tbody>[/TABLE]

formula:-

=IFERROR(INDEX('GRID (2)'!$C$3:$F$23,MATCH(1,--('GRID (2)'!$A$3:$A$23=A2)*--('GRID (2)'!$B$3:$B$33=E2)*--(AA2>=85)*--(I2>=95),0),MATCH(D2,'GRID (2)'!$C$2:$F$2,0)),"0")

 

[sachin483]

Can someone help me out on array formula