Lookup or If Statement?

[lcfierro]

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

ideas?

 

[schielrn]

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.

 

[lcfierro]

Whoops!

I've revised the post

 

[lcfierro]

 

[Colin Legg]

I don't know if anyone else has the same issue, but I cannot view the pictures on your first post.

 

[lcfierro]

Anyone else having trouble viewing the screen shots?

I can see them fine? Hummpf.

 

[jeffreybrown]

I can see the pictures in the first post, but your description of what you desire should be clearer. What are your expected results?

 

[lcfierro]

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?

 

[jeffreybrown]

For the first part it seems all you need is =VLOOKUP(B10,I1:J6,2,0)

Change any ranges to suit your layout.

 

[lcfierro]

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?