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

DIV	ACH	A
ZZ	100	16876
ZZ	101	17976
ZZ	102	19076
ZZ	103	20176
ZZ	104	21276
ZZ	105	22376
ZZ	106	25996
ZZ	107	27516
ZZ	108	29036
ZZ	109	30556
ZZ	110	32076
ZZ	111	34861
ZZ	112	36496
ZZ	113	38131
ZZ	114	39766
ZZ	115	41401
ZZ	116	44716
ZZ	117	46456
ZZ	118	48196
ZZ	119	49936
ZZ	120	51676

<tbody>
</tbody>

DD	WW	ACH	PAY	AA2	I2
ZZ	A	106	25996	85	95
ZZ	A	120	51676	85	95
ZZ	A	125	0	85	95

<tbody>
</tbody>

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