lookup function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi

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

Andy.
 
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...
 
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)
 
Why leave that out? That was a useful addition.
Maybe not in layman's terms, so maybe you could provide an example of both.
 
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 !
 
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...:(
 
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...:(
 
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)
 
Layout a bit messed up
So, column A:
0
200.000001
300.000001
1000

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