Formula help!!!!

[rudebwoyrohan]

I have a rate in G9 on TAB 1. I have ranges on TAB 2. This is what I need: If this rate is within the min (A2:A14) and max (B2:B14) of the values in the table on TAB 2 then choose the amount of the corresponding fee in [FONT=inherit !important][FONT=inherit !important]column C[/FONT][/FONT] (C2:C14).

VLOOKUP would be perfect only that the output of the lowest and the highest range is not a set number but a percentage. If G9 is equal or above A2 then the output is 15% of G9 but capped at 0.05 and if G9 is betw A14 and B14 then the output is 15% of G9.

PLEASE HELP!!! Thanks in advance guys!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RATE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]FEE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]INFINITY[/TD]
[TD]15% CAPPED AT 0.05[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]0.018
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]0.016[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]0.014[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]0.012[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]0.008[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]0.006[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]0.005[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]0.004[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]0.003[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0.002[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]15%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In H9 enter:

=INDEX(Sheet2!$C$2:$C$14,MATCH(G9,Sheet2!$A$2:$A$14,-1))