Formula for bonus payments

[djfatboyfats]

Hi please see the attached file, i have a bonus scheme which pays out depending on how much you have funded and how many payments have been collected, i need a formula that will automatically work this out for.

Please see the attached file for the full information

Thanks in advance

DJ

[teylyn]

Hi,

in F22 enter the formula

=INDEX($C$7:$M$15,MATCH(D22,$B$7:$B$15,1),MATCH(E22,{100,200,275},1)*3)*D22

and copy down. Formula will result in "N/A" if either funding is below 20,000 or payments collected are less than 100. If required, you can wrap the above in an IF statement to get rid of the displayed "N/A", like

=IF(ISNA(INDEX($C$7:$M$15,MATCH(D22,$B$7:$B$15,1),MATCH(E22,{100,200,275},1)*3)*D22),"no bonus",INDEX($C$7:$M$15,MATCH(D22,$B$7:$B$15,1),MATCH(E22,{100,200,275},1)*3)*D22)

hope that helps