Lookup or If Statement?

lcfierro

New Member
Joined
Mar 25, 2009
Messages
14
Hello all!

I'd like to ask for some quick answers to the question below ;) So, any assistance you can provide would be WONDERFUL!

The spreadsheet I'm working with contains commission line items for a salesperson over one quarter. I'm trying to determine a commission percentage based on the type of service the salesperson is selling. Then, based on the entire quarter sales, once they reach a threshold, the commission based on the type of service should increase.

Here, is the matrix for the commission. As you can see, services will have a code letter to reference on the matrix.

<a href="http://s62.photobucket.com/albums/h100/lcfierro/?action=view&current=Picture2-1.png" target="_blank"><img src="http://i62.photobucket.com/albums/h100/lcfierro/Picture2-1.png" border="0" alt="Photobucket"></a>

Here, I'll need to populate a formula in the column 'I', that relates to the matrix above. (Disregard the Commission Amount, this column has copied information in it.)

Picture3-1.png


ideas?
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
Welcome to the Board!

It appears that you have posted the same image, but from the looks of it, you will probably want to use a combination of index and match. You may need to change your column headers a little to make this work, but still depends on what you are looking for.
 
Upvote 0

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I don't know if anyone else has the same issue, but I cannot view the pictures on your first post.
 
Upvote 0

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,149
I can see the pictures in the first post, but your description of what you desire should be clearer. What are your expected results?
 
Upvote 0

lcfierro

New Member
Joined
Mar 25, 2009
Messages
14
I guess when I'm confused myself, I don't write clearly, LOL!

Okay, let's see.

I need to automatically determine a commission percentage. The commission percentage will depend on the product class. The product class basically relates to a group of service offerings. For example, the salesperson sells Integration Services, the Integration Services are part of a group B commission percentage. Group B calculates the booked value at 2% to determine the commission amount.

Then, it gets more complicated :)

It would be nice if there was just one percentage per product class. But, depending on the salesperson's total sales for the quarter, they are either at the threshold, at target, or excellence. Each of which has a different commission amount. (incentive commission). However, the additional commission only applies to the items after each sales tier (Threshold, target, excellence), not to all the line items.

does that help?
 
Upvote 0

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,149
For the first part it seems all you need is =VLOOKUP(B10,I1:J6,2,0)

Change any ranges to suit your layout.
 
Upvote 0

lcfierro

New Member
Joined
Mar 25, 2009
Messages
14
Awesome, worked!!

Any suggestions for approaching the second part? I'm thinking that I'll just do the same, but change the range in your formula, manually to the cells after a sales person has reached a certain sales goal for the quarter?
 
Upvote 0

Forum statistics

Threads
1,190,809
Messages
5,983,040
Members
439,815
Latest member
yoswosz

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
Top