lookup function

A

Anh

I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc. Then i have another table showing
different sales value of different workers. How do i find the commission rate
per worker using lookup functions??
Please help me..thx a lot
 
G

Guest

Hi

You could set up a table and use a VLOOKUP function. Post your breakdowns
and we'll have a go!!

Andy.
 
A

Anh

It's lika table like this:
Sales Comission rate
200 and under 0.50%
300 and under 0.75%
......................................................
1000 and under 2.50%

Then i have a list of different people with different sales figures:
Name Sales Comission rate
Anthony 487 ?
Bill 568 ?
.......................................
(up to 15 people)

I need to find the comission rate for each people by using lookup & the
table above. I do not know how to do it...
 
M

Max

Perhaps a sample construct for your situation would also help?
http://cjoint.com/?mzk3YMTmpY
Anh_wks.xls

Assuming the commission table is in Sheet1, in A1:B6

Sales Comission rate
200 0.50%
300 0.75%
500 2.50%
750 3.00%
1000 3.50%

And in Sheet2, sales are listed in B2 down

Name Sales Comission rate
Anthony 487
Bill 568
George 250
Peter 855
Goliath 1356

Put in C2, copy down:
=IF(B2="","",VLOOKUP(B2,Sheet1!A:B,2,TRUE))

For the sample,
we'd get the resulting comm rates in col C:

Name Sales Comission rate
Anthony 487 0.75%
Bill 568 2.50%
George 250 0.50%
Peter 855 3.00%
Goliath 1356 3.50%

(The above presumes a simple "flat" comm. rate structure,
not a complex "tiered" one)
 
N

Niek Otten

Why leave that out? That was a useful addition.
Maybe not in layman's terms, so maybe you could provide an example of both.
 
M

Max

Niek Otten said:
Why leave that out? That was a useful addition. ....
Click to expand...

My 2nd thought then was the line might arouse confusion rather than add
clarity.
Didn't want that to happen said:
Maybe not in layman's terms, so maybe you could provide an example of
Click to expand...
both.

Ah, think it's much better (and easier) to point to
JE's excellent treatment on the subject at his:

http://www.mcgimpsey.com/excel/variablerate.html

or, the direct link to the commissions example at:
http://www.mcgimpsey.com/excel/variablerate.html#commissions

Merry Christmas, Niek !
 
A

Anh

For the link Jones gave me, i already had a look..but in my case the table
showed the upper limit, not lower limit so the approximate match of Vlookup
did not return the correct answer..
And for the link Max gave me..it's very useful but quite far
complicated...In my case it's not marginal rate...and the answer u suggested
me seems not work..it keep on turn out to be #VALUE...:(
 
A

Anh

For the link Jones gave me, i already had a look..but in my case the table
showed the upper limit, not lower limit so the approximate match of Vlookup
did not return the correct answer..
And for the link Max gave me..it's very useful but quite far
complicated...In my case it's not marginal rate...and the answer u suggested
me seems not work..it keep on turn out to be #VALUE...:(
 
N

Niek Otten

Put this table in A1:B4

0 0.50%
200.000001 0.75%
300.000001 2.50%
1000 100%


With the sales amount in C1:

=VLOOKUP(C1,$A$1:$B$4,2)
 
N

Niek Otten

Layout a bit messed up
So, column A:
0
200.000001
300.000001
1000

Column B:
0.50%
0.75%
2.50%
100%
 
Top