Excel Magic Trick 453: VLOOKUP for Commission Brackets Calculation (LOOKUP function also)

See how to calculate commission on sales when there is more than one level of commission incentive pay. See how to make the lookup table smartly to reduce formula size. See how to use 3 VLOOKUP functions in one formula, including looking up values in the 1st, 2nd and 3rd columns. See how to do the same formula with LOOKUP function to reduce formula size and formula creation time.

Excel Magic Trick 452

Commission Bracket tables are similar to Tax Bracket Tables.

Category:

Science & Technology

Tags:

• Excel
• VLOOKUP
• function
• LOOKUP
• for
• Multi
• Layered
• Commission
• Calculation
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[MaJuarez2007]

Yeah VLookup makes it prety easy. I was using a bunch of nested IF functions but that was pretty messy whenever I needed to make changes.

Also, if you are calculating commissions in Excel, I have found a great tool called "OneClick Commissions" which allows sales managers to create professional statements from their Excel commissions spreadsheet.

[ExcelIsFun]

Cool!

[andes2]

soo good thanks

[ExcelIsFun]

You are welcome!

[madmansgates]

Hi Mike,Can you help? Commission rates: 0 - 20,000 - 10% 20,001 - 40,000 - 15% 40,001 - 60,000 - 20% 60,001 - 25% In any 3 months commission is paid monthly depending on sales per month over 3 months commision hurdle. eg: Month 1 Sales of 15k is 15k@10%=1500 commission Month 2 sales of 20k will have commision of 5k@10%(to reach 20k hurdle) and 15k@15% =total of 2750, Month 3 sales of 10k will have commission of  5k @15% (40k hurdle) & 5k at 20% total of 1750 any ideas? Thanks

[ExcelIsFun]

That is a great question! I do not know off the top of my head and right now I have family visiting and a heavy work load. Please post this to the Mr Excel Message Board:

mrexcel[dot]com/forum

Then send me the link via excelisfun YouTube e-mail!

[ExcelIsFun]

I just uploaded the workbook!

[ExcelIsFun]

Thanks for the heads up! I will upload it tomorrow morning!

[krn14242]

Mike

Didn't see the download workbook...

thanks