How to Calculate Commission in Excel Using VLOOKUP

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.


Download Practice Workbook

You can download the workbook and practice with them.


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.


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

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

Read More: How to Calculate Bonus Percentage in Excel (Step-by-Step Guide)


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

Read More: Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)


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

Read More: How to Calculate Sales Commission Formula in Excel (3 Easy Ways)


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.

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. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo