Commissions and lookup table

[ub67]

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?

 

[Ragdyer]

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.

 

[ub67]

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

Thank you!

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.

 

[RagDyeR]

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