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