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



--
Regards
Dave Hawley
www.ozgrid.com
"The Hit Man" <The Hit wrote in message
...
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