How to Calculate Commissions in Excel with VLOOKUP
27

How to Calculate Commissions in Excel with VLOOKUP

Bottom line: Learn how to calculate commissions in Excel for a basic tiered plan and rate table using the VLOOKUP function.

Skill level: Intermediate

Replace Complex IF Formulas with Simple Lookups

Calculating commissions in Excel can be a very tricky task.  This is especially true if you have tried to use multiple IF statements to calculate commissions for each tier in a rate table.

This article will explain how to use the VLOOKUP function to make this process much easier.  The secret is setting the last argument in the vlookup to TRUE, to find the closest match.

Please checkout my article on How to Use VLOOKUP to Find the Closest Match for a detailed explanation on setting the last argument to TRUE.
Download File

Download the file to follow along.

VLOOKUP Commissions Calculation Example.xlsx (21.6 KB)

Calculating Commissions with VLOOKUP

How to Use VLOOKUP to Calculate Commissions in Excel

With a simple commission plan you typically have a rate table that lists the payout rate at each level of sales.  As the sales rep makes more sales, his/her payout rate will typically increase.

The payout rate could be a flat dollar amount, percentage of revenue, percentage of quota, etc.  The job of the VLOOKUP is to find the rep’s sales amount in the rate table, and return the corresponding payout rate.

For this example our commissions plan looks like the following:

  • Rep sells $0-$50,000, they earn 5%
  • Rep sells $51,000-$100,000, they earn 7%
  • Rep sells $100,001-$150,000, they earn 10%
  • Rep sells over $150,001, they earn 15%

The rep will make a certain amount of sales during the month.  He/she will earn a certain payout rate depending on the level of sales achieved.

Calculate Commissions with VLOOKUP Closest Match

We can use a VLOOKUP formula to calculate the payout rate for a given sales amount (lookup value).  For this to work we need to set the last argument in the vlookup [range_lookup] to TRUE.

With the last argument set to TRUE, vlookup will find the closest match to the lookup value that is less than or equal to the lookup amount.  This basically allows us to find a value between ranges of two numbers (tiers).

How to Setup the Rate Table

When putting this rate table in Excel, you only need to list the tier minimum for the lookup range.  Again the VLOOKUP will search for a “closest match” that is less than or equal to the lookup value.  If it finds a value that is greater than the lookup value, then it will return the previous row.

Setup Rate Table for Commission Calculations with VLOOKUP

In this example for a commissions rate table, the first row in the lookup range needs to be zero.  This is because the sales rep could potentially have sales of $0 and the lookup value would be zero.  If the lookup value (sales amount) were a negative number, then the vlookup would return an error.

It is important to know this and setup your rate table for all possible lookup values.

If the sales amount is greater than the last row in the lookup range, then the vlookup will return the last row.  For example, if the rep made sales of $175,000 then vlookup would return 15%.

Calculate Commissions to Return a Dollar Value

The payout can also be returned as a dollar value, instead of a percentage.  With this setup the payout will be a flat rate.

This means that the payout will be the same, regardless of what the sales amount is within the tier.  In the example below, if the payout will be $1,000 if the sales amount is $55,000 or $95,000.

Setup Rate Table for Commission Calculations with VLOOKUP for Dollar Value

The payout is NOT on a sliding scale.  It is a flat rate for each tier.

If you are looking for a sliding scale calculation, see my article on calculating commission with a tiered rate structure using SUMPRODUCT.

Do NOT Use Nested IFs

A common approach to calculating commissions is using IF statements.  With a rate table like this you would have to write multiple IF statements.  You basically have to write one IF statement for each tier (row) in the table.

You then have to join all the IF statements into one long and ugly formula.  These are called nested IF statements.

Nested IF Statement for Commissions Calculations in Excel

I always try to avoid nested IFs when possible because they are difficult to read and understand, and they can be slower for Excel to calculate.  If you have thousands of nested IF formulas in your workbook, your calculation time could slow down.

Using VLOOKUP is much easier and cleaner than using nested IF formulas.  As you can see with this example, the vlookup allows you to use one formula to calculate the commission payout rate for any given sales amount.

Additional Resources

How Do You Use VLOOKUP with Closest Match?

The vlookup with closest match technique can also be used for tax bracket calculations, price matching, etc.  What do you use this technique to calculate?

Please leave a comment below with any questions or suggestions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 27 comments
Laurie - August 18, 2017

Hi

How do you suggest I use the VLookup formula if the commission pay out is % of Sales against target at a flat ate per tier achieved
Eg

105%+ 2.20%
97% – 104% 2.00%
91%-96% 1.80%
85 to 90 1.60%
0%-84% 1.00%

SALES AMOUNT TARGET % Achieved Payout
450,000 500,000 90% 7200

Reply
Lee - February 12, 2017

Under the “Calculate Commissions to Return a Dollar Value” heading.
Payout rate should be $500 not $1000 its in the wrong category. I am correct or incorrect?

Reply
MikeW - February 3, 2017

Hello,

I wanted to post a comment/question but needed to upload an example Workbook to illustrate. Is there any way to do that?

Thanks.

Reply
    Jon Acampora - February 4, 2017

    Hi Mike,
    Do to the volume of comments on the site, I’m not able to look at individual workbooks at this time. I apologize for the inconvenience.

    Reply
kaydian - November 12, 2016

good night really need some help with excel this is my first time at it.

Reply
Gresh - October 15, 2016

Hi, How do you work out a percentage on one sheet by using the total from another

Reply
George Hodges - October 13, 2016

I have 4 individuals, each with differing commission percentages of the total amount of commission. two of the individuals have a max out point, where they stop receiving a percentage of the total amount of commission. once they have maxed out and this is at different points in real time…. the percentages get proportionaltly get allocated to the other two employees. I have calculated with aN IF statement to return a commission amount for each employee. Question: i need to take into account the commission amount for each employee when that total amount of commission crosses the threashold for the max out point for the two employees? It will be a small varicance but the total commission for an employee will consist of differing percentages if in that month the amount of commission going to this sales department pushes the total into a different tier of commission [percentages.

Reply
trisha - September 27, 2016

Hi 😀
how do you do it if there is more than one pay out rate 😀 since in the problem im solving has three different ranks and each rank has an equivalent pay out percentage for each tier and rank 😀

Reply
Andy - September 6, 2016

I created this pivot table: I need to calculate the commissions for each of the three individuals by month. It is one commission figure, 5%.

Sum of Sales Sales Reps
Row Labels Bill Jane Joe Grand Total
Organic $23,336.00 $69,742.00 $73,096.00 $166,174.00
Jan $0.00 $12,958.00 $0.00 $12,958.00
Feb $0.00 $4,369.00 $6,326.00 $10,695.00
Mar $13,904.00 $9,121.00 $0.00 $23,025.00
Apr $0.00 $7,498.00 $23,431.00 $30,929.00
May $9,432.00 $20,134.00 $16,302.00 $45,868.00
Jun $0.00 $15,662.00 $27,037.00 $42,699.00
Red $95,536.00 $111,937.00 $91,888.00 $299,361.00
Jan $3,407.00 $3,287.00 $50,272.00 $56,966.00
Feb $23,859.00 $17,315.00 $11,310.00 $52,484.00
Mar $0.00 $11,649.00 $14,567.00 $26,216.00
Apr $16,847.00 $31,153.00 $5,582.00 $53,582.00
May $29,446.00 $8,559.00 $10,157.00 $48,162.00
Jun $21,977.00 $39,974.00 $0.00 $61,951.00
White $127,049.00 $88,126.00 $86,529.00 $301,704.00
Jan $31,114.00 $32,345.00 $10,694.00 $74,153.00
Feb $18,981.00 $8,939.00 $36,359.00 $64,279.00
Mar $16,847.00 $17,428.00 $17,640.00 $51,915.00
Apr $46,950.00 $0.00 $4,276.00 $51,226.00
May $8,516.00 $13,738.00 $11,953.00 $34,207.00
Jun $4,641.00 $15,676.00 $5,607.00 $25,924.00
Grand Total $245,921.00 $269,805.00 $251,513.00 $767,239.00
Sales Rep Monthy Average Total Sales by WineType
Organic $3,889.33 $11,623.67 $12,182.67 $27,695.67
Red $15,922.67 $18,656.17 $15,314.67 $49,893.50
White $19,187.00 $11,156.20 $15,167.00 $45,510.20

Reply
    Jon Acampora - September 7, 2016

    Hi Andy,

    You can probably just multiply the totals by 5%. This can be done with a calculated field in a pivot table. I hope that helps get you started.

    Thanks!

    Reply
prabu - August 10, 2016

Dear Friends,

Please give me a solution for this:

1. Customer A buys a product $1000 and i give 100 referral coupons.
2. Referred customer B buys a product worth any amount , i want to give 10% commission to Customer A till it become $1000. So that Customer A gets back his purchase value as a commission.

Request excel based formula for this.

Regs/Prabu

Reply
Ryan Tremain - May 14, 2016

Good morning Jeff

I have noticed you have some really helpful replies to the previous enquiries regarding there commission structure platform.
I am hoping you can point me in the right direction too.

I am currently selling a product at $169 with a commission structure with sales bonuses.

I currently pay my reps $50 1-4 sales
$60 ongoing after that.

I pay my reps a bonus full payment for the 8th sale and 13th sale. Rep receives full $169 for them sales.

This is a daily commission incentive.

Thankyou Jeff for reading and I hope you can help me with my template.

Kind regards Ryan Tremain

Reply
Baha - April 21, 2016

Sir I’m looking for excel which cover sales based on achievement with relation to achivement of distribution product wise volume vise and value wise. Also will get difference in last amount depending in his outstanding amount delayed or in time. And then team overall achievement set comission . can you advice

Reply
Zoe - April 11, 2016

How do I make a vlookup to report sales commission as a dollar value?

Reply
    Jon Acampora - April 14, 2016

    Hi Zoe,
    Great question! You can change the percentages in column D to dollar values. This will pay out a flat rate when the rep achieves a certain tier.

    I added a section above with a screenshot and explanation for returning the dollar value. I also added a sheet to the example file with an explanation.

    If you want to calculate a cumulative amount, checkout my article on how to calculate commissions with a tiered rate structure using SUMPRODUCT. This is a more advanced formula, but that is required for that type of calculation.

    Please let me know if you have any questions.

    Reply
Happy Titus - February 22, 2016

This is very useful

Reply
Ram Choudhary - December 27, 2015

Hello Sir,

I want maintain commission of my team in excel can you suggest me.
Example : 5 team members in my team
Member- 5 under in Member 4 – 10% (Commission rate with level)
Member-4 under in Member 3 – 5% (Commission rate with level)
Member-3 under in Member 2 – 2.5% (Commission rate with level)
Member-2 under in Member 1 – 1.5% (Commission rate with level)
Member 1 is no under any – 1% (Commission rate with level)

If member 5 do get commission then auto add all level

Reply
Yogarajah - September 29, 2015

Helps to learn short cuts and new methods.
Thanks

Reply
Michael Sasarman - July 9, 2015

OK – that didn’t come our right

(T-A) 80-89%, BM=50%
(T-A) 90-99%, BM=75%
(T-A) 100-125%, BM=100%
(T-A) 125-150%, BM=125%
(T-A) >150%, BM=150%

Reply
Michael Sasarman - July 9, 2015

Sorry, the name is Michael

Reply
Michael Sasarman - July 9, 2015

Hi,

I have a variation on the Bonus calculation formula – the Bonus Multiplier (BM)!

Each month a sales rep has a Sales Target (S) and realizes an Actual revenue (A).
The Bonus is 0.1% (B).
The Bonus payout is as follows:

* If A is 80% and = 90% and = 100% and = 125% and 150% of T, then BM is 150% of B

Tried to use VLOOKUP but since the Tiers are variables that change per employee and per month, it is more complicated.

Any thoughts?

Thanks in advance.

Reply
    Jon Acampora - July 10, 2015

    Hi Michael,
    When you say the tiers change per employee, is there any type of logic there? Why does each employee have a different tier structure? Maybe I’m not fully understanding your plan.

    Thanks

    Reply
Jeff Weir - April 7, 2015

Me again. Wow: my testing shows that the only time that IF outperforms VLOOKUP is when you only have two commission bands, meaning you can get by with just the one IF.

So never use a nested IF. Use VLOOKUP instead.

Far out.

Reply
Jeff Weir - April 6, 2015

Hi Jon. You’ve shown IF in a slightly unflattering manner, because your IF formula is twice as complex as it needs to be.

You can just use this:
=IF(F2>=$C$7,$D$7,IF(F2>=$C$6,$D$6,IF(F2>=$C$5,$D$5,$D$4)))

…which is slightly slower than VLOOKUP across 200,000 rows of data on your sample table, but there’s not much in it.

VLOOKUP: 0.31 seconds for 200,000 rows of data
IF: 0.38 seconds for all 200,000 rows of data

And if we restructure that IF statement we can shave 0.2 seconds further off of it:
=IF(F2>=$C$6,IF(F2>=$C$7,$D$7,$D$6),IF(F2>=$C$5,$D$5,$D$4))

Just for kicks, I tried this on a much bigger Commissions table – one with 15 bands. It took me a long time to whip up the efficient IF statement required to process the table intelligently: here’s what it looks like:
=IF(F2>=$C$10,IF(F2>=$C$14,IF(F2>=$C$16,IF(F2>=$C$18,$D$18,IF(F2>=$C$17,$D$17,$D$16)),IF(F2>=$C$15,$D$15,$D$14)),IF(F2>=$C$12,IF(F2>=$C$13,$D$13,$D$12),IF(F2>=$C$11,$D$11,$D$10))),IF(F2>=$C$7,IF(F2>=$C$9,$D$9,IF(F2>=$C$8,$D$8,$D$7)),IF(F2>=$C$5,IF(F2>=$C$6,$D$6,$D$5),$D$4)))

That takes about 0.61 seconds to process across 200,000 rows of data. Your VLOOKUP: 0.36 seconds.

VLOOKUP wins, hands down.

Reply
    Jon Acampora - April 7, 2015

    Hey Jeff,

    Thanks for keeping me honest! 🙂 I was trying to show the longest, scariest, ugliest IF statement possible. But you are right, it can definitely be shortened and I will fix the image to show that.

    Besides calculation time, the IF statements are definitely slower to write and take more time to read. You are having to reference almost every cell in the table with the IF statement. With the VLOOKUP you are only need to reference two ranges (lookup value and lookup range).

    I’m not completely opposed to nested IFs, but in this situation the VLOOKUP can be more efficient and easier.

    Thanks again Jeff! As always, I really appreciate the comment.

    Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x