Using Index Match to return values where both row and columns are a range

[Lokjor]

Hello!

I am trying to find a formula where, using a static book size (say $1550000) and dollars over goal (say 250000) it will return the % at which commission is applied & can then calculate the annual bonus. For the example this would be 4% as the book size is between $1.5m and $2m, and the total over goal between $200k and $300k.

Please see the grid, where both are the ranges.

​

​

​

​

​

​

 

[JamesCanale]

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G
1		Total Goal
2	Book Size	$0	$100,000	$200,000	$300,000	$400,000	$500,000
3	$400,000	1.00%	1.00%	2.00%	3.00%	4.00%	4.50%
4	$1,000,000	1.00%	2.00%	3.00%	4.00%	4.00%	5.00%
5	$1,500,000	2.00%	3.00%	4.00%	4.00%	5.00%	5.00%
6	$2,000,000	3.00%	4.00%	4.00%	5.00%	5.00%	5.00%
7	$2,500,000	4.00%	4.50%	5.00%	5.00%	5.00%	5.00%
8	$3,000,000	4.50%	5.00%	5.00%	5.00%	5.00%	6.00%
9	$3,500,000	5.00%	5.25%	5.00%	5.00%	6.00%	6.00%
10	$4,000,000	5.25%	5.50%	5.00%	6.00%	6.00%	6.00%
11	$4,500,000	5.50%	5.75%	6.00%	6.00%	6.00%	6.00%
12	$5,000,000	5.75%	6.00%	6.00%	6.00%	6.00%	7.00%
13	$5,500,000	6.00%	6.25%	6.50%	6.75%	7.00%	7.25%
14
15
16	Thing	$1,550,000
17	Other Thing	$250,000
18	%	4.00%
Sheet7

Cell Formulas
Range	Formula
B18	B18	=INDEX(B3:G13,XMATCH(B16,A3:A13,-1),XMATCH(B17,B2:G2,-1))