LOOKUP VALUE IN MATRIX

[miva0601]

Could someone please help me with a formula that would pull in the rebate % based on a customer's Sales Volume and Compliance Ratio? For example, customer 47422, has Sales Volume of $109K and Compliance ratio of 1.002 or 100% -- in the matrix any volume equal to or greater than 100K and Compliance Ratio of equal to or greater than 90%, would return 24% as the Rebate Rate.

5.15.21 PPA & Admin Fee E.xlsx
	A	B	C	D	E	F
28	Rebate % Matrix
29		Compliance Ratio
30
31	Sales Volume	0%	70%	80%	85%	90%
32	$ 0.0	5%	7%	10%	15%	19%
33	$ 15,000.00	6%	8%	11%	16%	20%
34	$ 30,000.00	7%	9%	12%	17%	21%
35	$ 50,000.00	8%	10%	13%	18%	22%
36	$ 75,000.00	9%	11%	14%	19%	23%
37	$ 100,000.00	10%	12%	15%	20%	24%
38
39
40
41
42
43
44	Data:
45
46	Period	Customer #	Sales Volume	Compliance Ratio	Rebate %
47	Sep-20	47422	$109,537.65	1.002	24%	need formula that would pull in the rebate % based on the Sales Volume and Compliance Ratio
48	Sep-20	39494	$50,194.53	0.93	18%
49	Sep-20	39903	$60,340.24	0.75
50	Sep-20	39476	$146,524.56	1.01
51	Oct-20	39476	$360,951.95	0.93
52	Oct-20	39494	$469,241.43	0.895
Sheet1

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F
28	Rebate % Matrix
29		Compliance Ratio
30
31	Sales Volume	0%	70%	80%	85%	90%
32	$0.00	5%	7%	10%	15%	19%
33	$15,000.00	6%	8%	11%	16%	20%
34	$30,000.00	7%	9%	12%	17%	21%
35	$50,000.00	8%	10%	13%	18%	22%
36	$75,000.00	9%	11%	14%	19%	23%
37	$100,000.00	10%	12%	15%	20%	24%
38
39
40
41
42
43
44	Data:
45
46	Period	Customer #	Sales Volume	Compliance Ratio	Rebate %
47	9/1/2020	47422	$109,537.65	100%	24%
48	9/1/2020	39494	$ 50,194.53	93%	22%
49	9/1/2020	39903	$ 60,340.24	75%	10%
50	9/1/2020	39476	$146,524.56	101%	24%
51	10/1/2020	39476	$360,951.95	93%	24%
52	10/1/2020	39494	$469,241.43	90%	20%
Sheet11

Cell Formulas
Range	Formula
E47:E52	E47	=INDEX($B$32:$F$37,MATCH(C47,$A$32:$A$37),MATCH(D47,$B$31:$F$31))

I get 22% for your second example.

 

[miva0601]

How about:

Book1
	A	B	C	D	E	F
28	Rebate % Matrix
29		Compliance Ratio
30
31	Sales Volume	0%	70%	80%	85%	90%
32	$0.00	5%	7%	10%	15%	19%
33	$15,000.00	6%	8%	11%	16%	20%
34	$30,000.00	7%	9%	12%	17%	21%
35	$50,000.00	8%	10%	13%	18%	22%
36	$75,000.00	9%	11%	14%	19%	23%
37	$100,000.00	10%	12%	15%	20%	24%
38
39
40
41
42
43
44	Data:
45
46	Period	Customer #	Sales Volume	Compliance Ratio	Rebate %
47	9/1/2020	47422	$109,537.65	100%	24%
48	9/1/2020	39494	$ 50,194.53	93%	22%
49	9/1/2020	39903	$ 60,340.24	75%	10%
50	9/1/2020	39476	$146,524.56	101%	24%
51	10/1/2020	39476	$360,951.95	93%	24%
52	10/1/2020	39494	$469,241.43	90%	20%
Sheet11

Cell Formulas
Range	Formula
E47:E52	E47	=INDEX($B$32:$F$37,MATCH(C47,$A$32:$A$37),MATCH(D47,$B$31:$F$31))

I get 22% for your second example.

You're right and thank you so much for the formula - it's exactly what I was looking for!

 

[Eric W]

Glad it works for you! Thanks for the update.