Calculate Rebate % Based On 2 Criteria - Sales Volume & Compliance Ratio - INDEX/MATCH?

[miva0601]

I need to calculate the Rebate % earned based on two criteria: sales volume & compliance ratio - the 3 columns I'm trying to complete are in red: Tier, Rebate % & Rebate Amount.


Question: What is the rebate amount for customer 121761 for September? Sales Volume is over $100K and Compliance is over 90%, so the rebate % earned would be 24%.



Matrix:


[TABLE="width: 453"]
<tbody>[TR]
[TD]

[/TD]
[TD="colspan: 2"]Compliance Ratio
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales Volume[/TD]
[TD="align: center"]0%
[/TD]
[TD]70%
[/TD]
[TD="align: center"]80%
[/TD]
[TD]85%
[/TD]
[TD]90%
[/TD]
[/TR]
[TR]
[TD] $ 0.0
[/TD]
[TD="align: center"]5%
[/TD]
[TD]7%
[/TD]
[TD="align: center"]10%
[/TD]
[TD]15%
[/TD]
[TD]19%
[/TD]
[/TR]
[TR]
[TD] $ 15,000.00
[/TD]
[TD="align: center"]6%
[/TD]
[TD]8%
[/TD]
[TD="align: center"]11%
[/TD]
[TD]16%
[/TD]
[TD]20%
[/TD]
[/TR]
[TR]
[TD] $ 30,000.00
[/TD]
[TD="align: center"]7%
[/TD]
[TD]9%
[/TD]
[TD="align: center"]12%
[/TD]
[TD]17%
[/TD]
[TD]21%
[/TD]
[/TR]
[TR]
[TD] $ 50,000.00
[/TD]
[TD="align: center"]8%
[/TD]
[TD]10%
[/TD]
[TD="align: center"]13%
[/TD]
[TD]18%
[/TD]
[TD]22%
[/TD]
[/TR]
[TR]
[TD] $ 75,000.00
[/TD]
[TD="align: center"]9%
[/TD]
[TD]11%
[/TD]
[TD="align: center"]14%
[/TD]
[TD]19%
[/TD]
[TD]23%
[/TD]
[/TR]
[TR]
[TD] $ 100,000.00
[/TD]
[TD="align: center"]10%
[/TD]
[TD]12%
[/TD]
[TD="align: center"]15%
[/TD]
[TD]20%
[/TD]
[TD]24%
[/TD]
[/TR]
</tbody>[/TABLE]


Data:
[TABLE="width: 1521"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 818"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 780"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Customer[/TD]
[TD]Agreement[/TD]
[TD]Sales Volume[/TD]
[TD]Compliance Ratio[/TD]
[TD]Tier[/TD]
[TD]Rebate %[/TD]
[TD]Rebate Amount[/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]9/30/2018[/TD]
[TD]121761[/TD]
[TD]47422[/TD]
[TD]$ 109,537.65[/TD]
[TD]1.002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]9/30/2018[/TD]
[TD]80437[/TD]
[TD]39494[/TD]
[TD]$ 50,194.53[/TD]
[TD]0.930[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]9/30/2018[/TD]
[TD]638392[/TD]
[TD]39903[/TD]
[TD]$ 60,340.24[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]9/30/2018[/TD]
[TD]77701[/TD]
[TD]39476[/TD]
[TD]$ 146,524.56[/TD]
[TD]1.010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2018[/TD]
[TD]10/31/2018[/TD]
[TD]375991[/TD]
[TD]39476[/TD]
[TD]$ 360,951.95[/TD]
[TD]0.930[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2018[/TD]
[TD]10/31/2018[/TD]
[TD]39449[/TD]
[TD]39494[/TD]
[TD]$ 469,241.43[/TD]
[TD]0.895[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Proposed Formulas:
[TABLE="width: 750"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tier[/TD]
[TD]=IF(F2>0.9,"90%+",IF(F2>0.85,"85-89.99%",IF(F2>0.8,"80-84.99%",IF(F2>0.7,"70-9.99%","0-69.99%")))) Is there a cleaner formula to pull in the tier range?[/TD]
[/TR]
[TR]
[TD]Rebate %[/TD]
[TD]Need help with formula[/TD]
[/TR]
[TR]
[TD]Rebate Amount[/TD]
[TD]=H2*E2[/TD]
[/TR]
</tbody>[/TABLE]


Format for the Tier column return value:
[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Tier Range [/TD]
[/TR]
[TR]
[TD]0-69.99%[/TD]
[/TR]
[TR]
[TD]70-79.99%[/TD]
[/TR]
[TR]
[TD]80-84.99%[/TD]
[/TR]
[TR]
[TD]85-89.99%[/TD]
[/TR]
[TR]
[TD]90%+[/TD]
[/TR]
</tbody>[/TABLE]

 

[miva0601]

Any suggestions on whether I should reword this to make my question more clear or more concise to get a response?