Commission Calculator

[Deetz74]

I'm trying to build basic commission calculator - unsure if VLOOKUP or IF or combo etc - I've attached the schedule if anyone could complete:

Basically no comms are payable unless someone above 70% YTD (cumulative) - they are paid at a variable rate (depending on YTD %) and they can catch up / get back pay on any missed $ through the year so always shows whats been earned YTD vs. whats been paid YTD - the balance owing is the result / commissionable amount YTD %

% YTD % of base rate
<70%? 0%
70% to 79% 70%
80% to 89% 80%
90% to 99% 90%
100% to 109% 100%
110% to 119% 110%
120% to129% 120%
130%+ 130%