Commissions and lookup table
 

Need some help please!

I sell items on consignment and collect commission on a sliding scale. I
have different categories for different customers. For example, I have
'John' set up for me to collect a percentage based on Table A. 'Pete' is set
up for Table B.

In my workbook, I have sheet 1 as follows:

Consignor Consignor ID Sale amount Commission Amount

John A 1400.00 ?
Pete B 1800.00 ?

Sheet 2 has the following info:

Consignor ID $ 0 - 500 $500.01-1500 $1500.01-3500
$3500.01+

A 25% 20% 18%
15%
B 20% 20% 17%
10%

I thought that I could do a lookup formula where the commission amount would
be calculated something like:

Look at the consignor id, then in sheet 2 find the percentage to multiply to
the sale amount in sheet 1.

The end result would be that I earn a commission from John of $ 280 and from
Pete $ 306.

Any thoughts?

Commissions and lookup table
 

First, change your column headings on Sheet2 to show *only* the lower
amounts:
0, 500.01, 1500.01, 3500.01

Then try this in D2 of Sheet1:

=C2*INDEX(Sheet2!$A$1:$E$3,MATCH(B2,Sheet2!$A$1:$A $3,0),MATCH(C2,Sheet2!$A$1
:$E$1))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ub67" wrote in message
...
Need some help please!

I sell items on consignment and collect commission on a sliding scale. I
have different categories for different customers. For example, I have
'John' set up for me to collect a percentage based on Table A. 'Pete' is
set
up for Table B.

In my workbook, I have sheet 1 as follows:

Consignor Consignor ID Sale amount Commission
Amount

John A 1400.00
?
Pete B 1800.00
?

Sheet 2 has the following info:

Consignor ID $ 0 - 500 $500.01-1500 $1500.01-3500
$3500.01+

A 25% 20% 18%
15%
B 20% 20% 17%
10%

I thought that I could do a lookup formula where the commission amount
would
be calculated something like:

Look at the consignor id, then in sheet 2 find the percentage to multiply
to
the sale amount in sheet 1.

The end result would be that I earn a commission from John of $ 280 and
from
Pete $ 306.

Any thoughts?

Commissions and lookup table
 

Can I just say - -you are AWESOME!!!

Thank you!

"Ragdyer" wrote:

First, change your column headings on Sheet2 to show *only* the lower
amounts:
0, 500.01, 1500.01, 3500.01

Then try this in D2 of Sheet1:

=C2*INDEX(Sheet2!$A$1:$E$3,MATCH(B2,Sheet2!$A$1:$A $3,0),MATCH(C2,Sheet2!$A$1
:$E$1))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ub67" wrote in message
...
Need some help please!

I sell items on consignment and collect commission on a sliding scale. I
have different categories for different customers. For example, I have
'John' set up for me to collect a percentage based on Table A. 'Pete' is
set
up for Table B.

In my workbook, I have sheet 1 as follows:

Consignor Consignor ID Sale amount Commission
Amount

John A 1400.00
?
Pete B 1800.00
?

Sheet 2 has the following info:

Consignor ID $ 0 - 500 $500.01-1500 $1500.01-3500
$3500.01+

A 25% 20% 18%
15%
B 20% 20% 17%
10%

I thought that I could do a lookup formula where the commission amount
would
be calculated something like:

Look at the consignor id, then in sheet 2 find the percentage to multiply
to
the sale amount in sheet 1.

The end result would be that I earn a commission from John of $ 280 and
from
Pete $ 306.

Any thoughts?

Commissions and lookup table
 

You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"ub67" wrote in message
...
Can I just say - -you are AWESOME!!!

Thank you!

"Ragdyer" wrote:

First, change your column headings on Sheet2 to show *only* the lower
amounts:
0, 500.01, 1500.01, 3500.01

Then try this in D2 of Sheet1:

=C2*INDEX(Sheet2!$A$1:$E$3,MATCH(B2,Sheet2!$A$1:$A $3,0),MATCH(C2,Sheet2!$A$1
:$E$1))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ub67" wrote in message
...
Need some help please!

I sell items on consignment and collect commission on a sliding scale.
I
have different categories for different customers. For example, I have
'John' set up for me to collect a percentage based on Table A. 'Pete'
is
set
up for Table B.

In my workbook, I have sheet 1 as follows:

Consignor Consignor ID Sale amount Commission
Amount

John A 1400.00
?
Pete B 1800.00
?

Sheet 2 has the following info:

Consignor ID $ 0 - 500 $500.01-1500 $1500.01-3500
$3500.01+

A 25% 20% 18%
15%
B 20% 20% 17%
10%

I thought that I could do a lookup formula where the commission amount
would
be calculated something like:

Look at the consignor id, then in sheet 2 find the percentage to
multiply
to
the sale amount in sheet 1.

The end result would be that I earn a commission from John of $ 280 and
from
Pete $ 306.

Any thoughts?