Sabrina Ayon
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.
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.
- Further, to replicate the formula throughout the range, drag the Fill Handle downward. To AutoFill the range, double-click on the plus (+) symbol.
- And, finally, by doing this, you will get the result.
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.
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!
