Calculating tiered commission based on plan name and amount
in the file, how do I set up formula so that depending on what plan is chosen e.g. CPVP and the amount of the sale a percentage is chosen? e.g. for plan CPVP and amounts 0 to 100000 for % commission is .06 (E4). For the plan FFVP it's .045 (E9). I've experimented with Vlookups (using true - which has given me the correct % for amounts ) and if functions (I tried if with the and function and vlookup but that didn't work) but I want it to be dynamic so that I can easily add plans etc. I'm suspecting some variety of Sumproduct...It's going to be linked to a list where people will be picking the list from a name. Thank you as always. commission_calcs.xlsx