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

**Skill level:** Intermediate

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

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.

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.

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.

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.

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 to Use VLOOKUP to Find the Closest Match – Last Argument Equals TRUE
- How to Calculate Cumulative Tiered Commissions with SUMPRODUCT
- VLOOKUP Explained in Simple Terms at Starbucks
- VLOOKUP & MATCH – A Dynamic Duo

## 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!

thank you so much for sharing this. I was using if but it has so many tiers. it’s painful.

You just saved me so much time with the commission tiers!

Please Slove this Problem

ITEM NO AMOUNT COMMISSION % COMMISSION Recived

DEPOSIT 200500 0.25% MIN RS2 MAX RS8 ?

WITHDRAWL 1500 0.50% MIN 3 MAX 10 ?

MONEY TRF 5000 0.25% MIN RS2 MAX RS8 ?

AEPS WITDRAWL 1000 0.50% MIN 3 MAX 10 ?

FUND TRF 10000 0.25% MIN RS2 MAX RS8 ?

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.

Help!

Thanks,

LTP

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

Example

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.

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!!!

I’d like to know this one too.

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%

Hi Nick

did you work out how to do your calculations

Cheers

Andrew

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

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

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?

Hi Lee,

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

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.

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.

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

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

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.

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 😀

Hi Trisha,

I have another article on commissions with rate tiers that might help. The SUMPRODUCT formula used there is definitely more complex, but the calculation is more complex as well.

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

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!

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

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

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

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

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.

This is very useful

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

Helps to learn short cuts and new methods.

Thanks

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%

Sorry, the name is Michael

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.

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

Me again. Wow: my testing shows that the

onlytime that IF outperforms VLOOKUP is when you only have two commission bands, meaning you can get by with just the one IF.So

neveruse a nested IF. Use VLOOKUP instead.Far out.

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.

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.