How to Calculate Commission in Excel Using VLOOKUP

Get FREE Advanced Excel Exercises with Solutions!

A commission is money earned from a sale. Companies often pay out a proportion of total sales income. It’s actually a payment made to an agent or employee for completing a transaction or providing a service. So, it’s important to know the calculation of the commission. We may use this formula to compute commission: Total Sales Revenue * Commission Rate = Commission. But for the accurate result excel has some functions. In the article, we will demonstrate the procedures to calculate commission in Excel using the VLOOKUP function.


VLOOKUP Function: Syntax and Arguments

The VLOOKUP function in Excel is a built-in function that makes it easy to find over several columns.

➧ Syntax

The syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

➧ Arguments

Argument Required/Optional Explanation
lookup_value Required This is the value to check for in a table’s first column.
table_array Required The range of all cells in the table.
col_index_num Required The range of all cells in the table.
[range_lookup] Optional True by default, if found the close contest otherwise false which denotes an exact match.

➧ Return Value

The matching value is out of a table.


How to Calculate Commission in Excel Using VLOOKUP: Step-by-Step Procedures

We use VLOOKUP when we need to search a table or a range by row. It’s much faster and simpler to use VLOOKUP. This enables us to compute the commission payment rate for just any specified total sales using only one calculation. Let’s get started calculating the commission using the VLOOKUP function in excel.


Step 1: Create Commission Level

Firstly, we need to create the commission levels. For this, we make 3 columns.

  • In column B, we insert the Tier Minimum for the percentage.
  • Then, column C has the Tier Maximum for the percentage.
  • Finally, in column D, we have the percentage for the range shown in the left-sided columns.

Step-by-Step Procedures to Calculate Commission in Excel Using VLOOKUP


Step 2: Compute Commission Rate Using VLOOKUP

The commission rate is the ratio or set payment connected with a specific sale amount. Now, to find the percentage or rate of the commission we have to follow the steps.

  • Firstly, select cell D12.
  • Secondly, insert the formula into that cell.
=VLOOKUP(C12,$B$5:$D$7,3,TRUE)
  • After that, to complete the operation hit the Enter key.

Step-by-Step Procedures to Calculate Commission in Excel Using VLOOKUP

  • Further, to replicate the formula throughout the range, drag the Fill Handle downward. To AutoFill the range, double-click on the plus (+) symbol.

Step-by-Step Procedures to Calculate Commission in Excel Using VLOOKUP

  • And, finally, by doing this, you will get the result.

Step-by-Step Procedures to Calculate Commission in Excel Using VLOOKUP


Step 3: Calculate Commission Earned in Excel

Now, we already know how much commission the employees will get. But to know the exact amount, we will now calculate the commission earned. For this, we are again using the same formula likewise the previous step. We will just modify the formula. Let’s see how to find the commission earned.

  • In the first place, choose the cell where you want to calculate the commission earned. So, we select cell E12.
  • Then, enter the following formula there.
=VLOOKUP(C12,$B$5:$D$7,3,TRUE)*C12
  • Finally, press Enter.

  • Furthermore, to copy the formula over the range, drag the Fill Handle down or double-click on the plus (+) icon.

  • Finally, you can see the commission earned which is mainly the commission rate of total sales.

How to Calculate Commission in Excel Using VLOOKUP


Things to Remember

  • Columns should be used to organize the data in the given table array.
  • The function considers both uppercase and lowercase text to be equal.
  • Numeric and textual approximations of values are not treated equally by the VLOOKUP function.

Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above procedures will assist you to Calculate Commission in Excel Using VLOOKUP. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.


<< Go Back to Commission Bonus | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo