Excel Commission Formula

Question

I wonder if I can get help to get the right formula to find out comm%based on the price at which it was sold.

Basically, there are two tables .

The first is our pricing grid to show what the commission pct will be:

Product  -  20%  -  15%   -  10%
Widget 1 -  3990 -  3490  -  3090
Widget 2 -  4990 -  4490  -  4090
Widget 3 -  5990 -  5490  -  5090

The second table will report on the Products sold and at what price it was sold at:

Widget 2 - $4690
Widget 3 - $5100
Widget 1 - $4000

I thought INDEX/MATCH. I can do the right thing MATCHto find the right line, but I'm not sure how to include this price range in the formula.

In the above example, the commission should be 15%, 10% and 20%, respectively.

, , , , % .

, . MATCH, , , , , .

.

Answer 1

, Hlookup, range_lookup. , % %. :

Product     10%     15%     20%
Widget 1    3090    3490    3990
Widget 2    4090    4490    4990
Widget 3    5090    5490    5990

, ,

=OFFSET(Sheet1!$A$1;0;MATCH(HLOOKUP(INT(SUBSTITUTE(B1; "$"; ""));INDIRECT("Sheet1!"&MATCH(A1;Sheet1!$A:$A ) & ":" & MATCH(A1;Sheet1!$A:$A ));1; TRUE);INDIRECT("Sheet1!"&MATCH(A1;Sheet1!$A:$A ) & ":" & MATCH(A1;Sheet1!$A:$A)) )-1)

:

( - int, - $)

SUBSTITUTE(B1; "$"; "")

, Hlookup. ":" .

INDIRECT("Sheet1!"&MATCH(A1;Sheet1!$A:$A ) & ":" & MATCH(A1;Sheet1!$A:$A ))

Hlookup :

HLOOKUP(INT(SUBSTITUTE(B1; "$"; ""));INDIRECT("Sheet1!"&MATCH(A1;Sheet1!$A:$A ) & ":" & MATCH(A1;Sheet1!$A:$A ));1; TRUE)

1:

MATCH(<hlookup>; INDIRECT("Sheet1!"&MATCH(A1;Sheet1!$A:$A ) & ":" & MATCH(A1;Sheet1!$A:$A))) - 1

,

Offset(Sheet1!$A$1;0; <match>)