IF Function for calculating Sales Incentive

Kindly help me out in creating the formula with IF function in calculating the sales incentive for different percentages of achivement against target set. I have uploaded the worksheet and requires the formula for coloumn D. The range of rates per car has been provided along with the worksheet.

Several ways of accomplishing this and personally a nested IF statement would be the last I'd use.

The below in D5:

Formula:

Please Login or Register  to view this content.

And this in E5:

Formula:

Please Login or Register  to view this content.

Would do what you need.

Or, the attachment uses an INDEX/MATCH lookup and a slightly amended version of your lookup table to do the same job.
Personally I prefer this approach as if you need to amend the incentives in the future it can be easier to amend the table than the formula.

Hope that helps

BSB

Another variation..

=IFERROR(VLOOKUP($C$5,$B$9:$C$13,2,1),0)