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

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

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  