vlookup help

K

Kelly frank

New Member
Joined
Sep 28, 2005
Messages
1
I need some help. I have to desing a spreadsheet for sales commissions that is based on percentage of budget reached. For example, if the salesperson reached 93% or less of budget, his commission percentage is 13%, if he reaches 94%-103% of budget his percentage is 15%, if he reacehs over 103% of budget his percentage is 17%. I have a spreadsheet for each saleperson that shows billing, budget and then calculates the percent to budget. How do I get the vlookup forumla set up to automatically enter the correct percentage based on the percent of budget reached?

ANy info would be great.
Thanks!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Welcome to the Board!

Assuming the percent to budget is in column D, try this formula:

=VLOOKUP(D1,{0,0.13;0.94,0.15;1.03,0.17},2)

Hope this helps,
 
Upvote 0
Why not just use if?

So if the percent of budget were in A2 you could get the percent commision by using this:

=IF(A2<0.93,0.13,IF(A2>1.03,0.17,0.15))
 
Upvote 0
buliao said:
I thought the best soluation is using IF function.
Click to expand...

Great. Care to elaborate why invoking an IF is the best solution if "I don't know VLOOKUP or LOOKUP" is not the motive behind your claim?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Calculating Tiered Commission Structure based on Rolling Annual Contribution
Replies
1
Views
915
Dave Patton
D
D
  • Question
VBA Code - the code is not pasting to the column I need it to.
Replies
2
Views
563
dragontbone
D
J
  • Question
Help modifying an existing macro to remove all of the "fixed" ranges in it
Replies
1
Views
506
mackc557
M
X
  • Question
Conditional formatting cells up to first cell after group of consecutive zeros
Replies
1
Views
292
awoohaw
awoohaw
F
  • Question
Please help me with these 3 vlook up problems. Excel Sheet is attached.
2
Replies
10
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,214,614
Messages
6,120,519
Members
448,968
Latest member
Ajax40

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top