Formula to obtain result from non exact sequence match

[The Hit Man]

I know this has to be easier than I currently do it. I have seen examples
similar using exact matches or dates but nothing using ranges. I can obtain
the correct answer when my sales value is an exact match on the commission
payment tier table but I have been unable to formulate this correctly for non
exact matches. My v-lookup will round to the nearest commission after I have
passed to the next level rather than the next higher increment. I have many
different rates by products and states and need a consistent means to return
the appropriate value based on the specific sales amount and commission rate
tier.


(a) Monthly sales amount to pay commission on
Sales Commission Correct commission answer
$5,000 formula 1000
$25,001 formula 1200
$35,000 formula 1300

(b) commission payment tiers
Sales Value Start Sales Value Stop Commission
0 20000 1000
$20,001 25000 1100
$25,001 30000 1200
$30,001 35000 1300

 

[ozgrid.com]

See
http://www.ozgrid.com/Excel/sliding-bracket.htm
and
http://www.ozgrid.com/VBA/sliding-bracket-vba.htm

 

[The Hit man]

The example I have given is simplified. I could have up to 40 rate tiers and
cover 40 different product lines. Maybe I was wrong as this is not as easy as
I thought it would be. I reviewed your options and am still hoping to avoid
VBA but that may turn out to be the way to go. The formula example did not
look like it would work for the number of tier ranges I have, Did I miss
something?