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 34 comments
LTP - June 14, 2018

Hi, I’m struggling to get a formula that works.

Employee is paid a bonus depending on whether objective is met and at which tier. In order to be eligible for the bonus, employee’s objective must be 100% at the minimum. If the objective is between 100-109.99% and employee achieved a survey score below 935, then employee will only be awarded $500. If objective is met and employee achieved a survey score at or over 935, employee will be awarded $1000. If objective is at 110%+ and employee achieved a survey score below 935, then employee is rewarded $1000. If objective is at 110%+ and survey score is at or above 935, employee is awarded $2000.



GeePers - April 1, 2018

Need to calculate commission based on min/max sell range. Ex
Product sells $500 (max) $450 (min)
Commission 10% at max
Commission 1% at min to $0.
Ques? What is commission if product sells at $437?

Also, need to calculate service contract with terms and interest
Customer purchases system for $2,000 pays deposit for $500 balance $1,500. Will pay balance in term. Terms are
12 months at 6%, = ? per month.
24 months at 7.5%, = ? per month.
36 months at 9.5%, = ? per month.
48 months at 11%, = ? per month.
60 months at 15%, = ? per month.

Thanks for your help.

Nick - January 3, 2018

Hi, I have slightly different scenario.

Assume someone sells something worth $120,000.

Every dollar up to $100,000 pays 7%
Every remaining dollar $100,001 to $499,999 pays 3.5%
Every remaining dollar $500,000 pays 2%

With this comp structure, $120,000 pays $7,700. $100,000 @ 7% and the remaining $20,000 @ 3.5%.

What is the formula for this setup?

Thank you!!!

    Wing - February 5, 2018

    I’d like to know this one too.

      ANDREW BURNS - May 31, 2018

      Me to i need to do one for our cut on sales

      this is mine
      0 1,000,000.00 10%
      1,000,000.00 2,000,000.00 9%
      2,000,000.00 3,000,000.00 8%
      3,000,000.00 4,000,000.00 7%
      4,000,000.00 5,000,000.00 6%
      5,000,000.00 6,000,000.00 5%
      6,000,000.00 250,000,000.00 3%

    ANDREW BURNS - May 31, 2018

    Hi Nick

    did you work out how to do your calculations



Nidhi - September 25, 2017

AHT Slab Weightage
140-142.9 80.0%
138-139.9 100.0%
136-137.9 110.0%
134-135.9 125.0%
130-133.9 150.0%

Emp Id Name AHT
46901 Asif Mondal 127
45768 Sandip Ghosh 105
46524 Kiran May Das 140
46622 Sourasish Paul 134
46617 Sabita Gupta 136
47791 Deepak Rajak 126
46722 Sandip Singha 136
48060 Jawed Iqbal 150
48049 Joy Bhattacharjee 131
46944 Abdul Mondal 141
46485 Bijon Sadhukhan 128

I have to find out AHT of above employee as per slab..pls help

Laurie - August 18, 2017


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

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

450,000 500,000 90% 7200

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?

    Jon Acampora - February 15, 2017

    Hi Lee,
    When the sales amount is $55,000 the payout is $1,000.

MikeW - February 3, 2017


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


    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.

kaydian - November 12, 2016

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

Gresh - October 15, 2016

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

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.

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 😀

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

    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.


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.


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

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

Zoe - April 11, 2016

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

    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.

Happy Titus - February 22, 2016

This is very useful

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

Yogarajah - September 29, 2015

Helps to learn short cuts and new methods.

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%

Michael Sasarman - July 9, 2015

Sorry, the name is Michael

Michael Sasarman - July 9, 2015


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.

    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.


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.

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:

…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:

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:

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

VLOOKUP wins, hands down.

    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.


Leave a Reply: