**Bottom line:** Learn how to calculate commissions in Excel for a basic tiered plan and rate table using the IF, VLOOKUP, or XLOOKUP functions.

**Skill level:** Intermediate

## Video Tutorial

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.

**Update:** The post has been updated to include instructions for calculating commissions with the new XLOOKUP function. Here is an article & video on XLOOKUP.

## Download File

Download the file to follow along.

## 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).

This is why the last argument in VLOOKUP is named **range_lookup**. When this argument is TRUE, VLOOKUP is looking between ranges of values in the tier minimum column to find an exact match or the value less than the lookup value.

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

## Commission Calculations with XLOOKUP

We can also use the new XLOOKUP function for this calculation. It works very similar to VLOOKUP when finding the closest match.

### Advantages with XLOOKUP

There are **two main differences and advantages with XLOOKUP**:

- With XLOOKUP we specify the
**lookup array and return array as separate ranges**. This is B4:B7 and D4:D7 in the image above. This is an advantage over VLOOKUP because we can insert or delete rows between columns B and D, and the formula will still work.

When we add/delete columns within the table array of VLOOKUP, we have to manually change the column index number or use a formula to dynamically calculate it. - XLOOKUP
**does NOT require the data to be sorted**when using Exact match or next smaller/larger item for match mode. XLOOKUP will actually look for the next smaller/larger item and return the result from that row/column.

VLOOKUP requires the data to be sorted when the last argument is TRUE. Typically you will want to sort your data for these types of tables anyways, but it's good to know that you can just put new rate tiers at the bottom of the table and XLOOKUP will work.

### Drawback of XLOOKUP

The main disadvantage with XLOOKUP is **compatibility**. Both you and all of the users of your file must be on a version of Microsoft/Office 365 that has XLOOKUP. It's is NOT backward compatible or available on older versions of Excel.

Therefore, VLOOKUP works great in this scenario and is much easier to write than a nested IF formula.

## Avoid Nested IF Formulas

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.

The IF formula is **much more difficult to maintain**. It requires modification when rows are added/deleted from the table. VLOOKUP and XLOOKUP do NOT require this type of maintenance.

If you do use IF, make sure to **make each cell reference to the rate table an absolute reference** (F4 on the keyboard). Otherwise you will get incorrect results when copying the formula 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
- The New XLOOKUP Function for Excel + Video Tutorial
- IF Function Explained: How to Write an IF Statement Formula in Excel

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

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.

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,

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

Sorry, the name is Michael

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%

Helps to learn short cuts and new methods.

Thanks

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

This is very useful

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.

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

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

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

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!

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 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 work out a percentage on one sheet by using the total from another

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

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.

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.

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

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

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

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 ?

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

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

I’ll pay someone to do one for me specifically for my needs.

Slab Call From-To Incentive

Slab 1 1841-2041 ₹ 2.50

Slab 2 2025-2116 ₹ 3.50

Slab 3 2117-2207 ₹ 4.50

Slab 4 2208-2300 ₹ 5.00

Slab 5 2301-Above ₹ 10.00

Hi Jon,

Using SWITCH(TRUE()) to achieve the same result but (to me) in a more readable format:-

=SWITCH(TRUE(),

SalesAmount>=$B$7,$D$7,

SalesAmount>=$B$6,$D$6,

SalesAmount>=$B$5,$D$5,

$D$4

)

Another way without a table:-

=SWITCH(TRUE(),

SalesAmount>=150000,15%,

SalesAmount>=100000,10%,

SalesAmount>=50000,7%,

5%

)

Thank you it is nice. but i have a problem to calculate remuneration tax.

Amount For married Tax rate

First 400000 – 400,000 1%

next 100000 400,001 500,000 10%

next 200000 500,001 700,000 20%

next 1300000 700,001 2,000,000 30%

up to 1300001 2,000,001 36%

Total Taaxable income is

2,506,483 Scenerio-1

1,506,483 Scenerio-2

For Unmarried

Figure Range-from Range-to Tax rate

First 350000 – 350,000 1%

next 100000 350,001 450,000 10%

next 200000 450,001 650,000 20%

next 1250000 650,001 1,950,000 30%

up to 12500001 19,500,001 36%

Tax rebate for female 10% on total tax.

I have an alternate solution to your XLOOKUP take on the sales commision.

Using named ranges in the ‘Commitions’ table, I used the FILTER function instead of XLOOKUP:

=FILTER(Commision_Rate.((T_Min=Sales_Amount)),””)

Oops! A ‘small’ glitch in the copy/paste. The formula should read:

=FILTRER(Commision_Rate;((T_Min=Sales_Amount)))

I typed vlookup as you did in the video, I can not get any results the only value that appears is #N/A, Do the cells need special formatting I am using excel 2000, this has worked in the past, I can not find out what is wrong now, any ideas. Thank you for your help.

Thank you for making the video available.

As personalization becomes more integral to marketing to customers, your businesses are looking at ways to apply this approach to coupons and other discounts the excitement of redeeming a coupon

Please help me calculate commission on a product

PLEASE HELP ME WITH BONUS ON BONUS SHEET OF IF+FUNCTION +CHALLENGE THIS IS MY FORMULA AND IT RETURNS NA

=VLOOKUP(C11,$B$4:$D$7,3,TRUE)

WHAT AM I DOING WRONG ?

How to calculate monthly installment for following markup slab range;

Principle: $30,000,000

Start Date: 10/10/2023

Interest slab range:

1st Slab- 1 to 200,000 – 2%

2nd Slab- 200,001 to 500,000 – 5%

3rd Slab- 500,001 to 700,000 – 7%

4th Slab- 700,000 to 1,000,000 – 10%

Loan tenure: 20 Years.

Monthly Installment : ?

This article offers a straightforward guide on using VLOOKUP for commission calculations in Excel. It highlights the benefits of setting the last argument to TRUE, making it easy for readers to grasp. The addition of XLOOKUP and its advantages is informative. The advice to avoid nested IF statements for clarity and maintenance is wise. An excellent resource for Excel users seeking commission calculation solutions.