# Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

## Calculate Commission by using vlookup

Posted by Chad on October 17, 2001 12:57 PM
I have a problem I really need help with as soon as possible. This problem involves VLOOKUP!!! I am to enter formulas in the worksheet to calculate the monthly commissions. The user will enter the monthly sales amount in a cell (B7), and your spreadsheet automatically will calculate the commision. The spreadsheet looks like this:

Monthly Sales: (input from user)
Commission Owed = \$xx,xxx(formula here)

Commission Table
for Manager
Sales Commission Rate
\$0 \$0 5%
\$50,000 \$1,500 8%
\$75,000 \$2,000 10%
\$100,000 \$2,500 12%
\$120,000 \$3,000 14%

Example: Sales of \$152,000
Sales Account Manager
\$3,000 + [(\$152,000 - \$120,000) X 14%]

 Check out our Excel Resources

## Re: Calculate Commission by using vlookup

Posted by IML on October 17, 2001 1:09 PM

Let say your table is in H1:J5. You could use
=VLOOKUP(B7,\$H\$1:\$I\$5,2)+((B7-VLOOKUP(B7,\$H\$1:\$H\$5,1))*VLOOKUP(B7,\$H\$1:\$J\$5,3))

good luck

## Re: Calculate Commission by using vlookup

Posted by Aladin Akyurek on October 17, 2001 1:09 PM

Select the table of commisssions excluding labels such as Sales, etc. Go to the Name Box on the Formula Bar and type CommisionTable.

Assuming that input from user is A1,

in A2 enter: =vlookup(a1,CommissionTable,2)+(a1-vlookup(a1,CommissionTable,2))*(vlookup(a1,CommissionTable,3))

If you prefer (I would),

in A2 enter: =vlookup(a1,CommissionTable,2)

in A3 enter: =a2+(a1-a2)*(vlookup(a1,CommissionTable,3))

to avoid looking up the same thing twice.