Lookup using multiple critieria across ranges

[LtCmdrData]

Hello Everyone,

I need help with a lookup formula that will allow me to enter two inputs and then lookup a value in a table that has multiple ranges (see below). The two inputs are weight and miles. Each of the inputs has multiple ranges to find the correct corresponding value. In essence it should look down and across in the table, in this case to find 0.15 and then multiple that times 2157 to equal 323.55. I am running Windows 10 with Office 365 but I don't have access to new formulas like XLOOKUP and FILTER yet so I need something older. I'm thinking maybe some type of INDEX/MATCH or SUMPRODUCT might work but I just don't know how to apply it given these number ranges. Any and all help would be much appreciated. Thank you.

					Miles
Weight	Miles	Rate		Weight	1-499	500-999	1000-1499	1500-1999	2000-9999
2157​	1350​	325.55		1-499	0.21​	0.23​	0.24​	0.33​	0.34​
				500-999	0.15​	0.19​	0.22​	0.24​	0.28​
				1000-1999	0.12​	0.15​	0.19​	0.21​	0.23​
				2000-9999	0.08​	0.13​	0.15​	0.16​	0.22​
				10000-19999	0.07​	0.07​	0.08​	0.11​	0.13​

 

[Fluff]

If you're happy to change the headers, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1						Miles
2	Weight	Miles	Rate		Weight	1	500	1000	1500	2000
3	2157	1350	323.55		1	0.21	0.23	0.24	0.33	0.34
4					500	0.15	0.19	0.22	0.24	0.28
5					1000	0.12	0.15	0.19	0.21	0.23
6					2000	0.08	0.13	0.15	0.16	0.22
7					10000	0.07	0.07	0.08	0.11	0.13
Summary

Cell Formulas
Range	Formula
C3	C3	=A3*INDEX(F3:J7,MATCH(A3,E3:E7,1),MATCH(B3,F2:J2,1))

 

[steve the fish]

If you change the weight to read 0,500,1000,2000,10000 and miles to 0,500,1000,1500 and 2000 then you can use:

=INDEX(F3:J7,MATCH(A3,E3:E7),MATCH(B3,F2:J2))

If you do need to take into account the upper weight and miles ie 19999 and 9999 you could put a check in there first.

 

[LtCmdrData]

If you're happy to change the headers, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1						Miles
2	Weight	Miles	Rate		Weight	1	500	1000	1500	2000
3	2157	1350	323.55		1	0.21	0.23	0.24	0.33	0.34
4					500	0.15	0.19	0.22	0.24	0.28
5					1000	0.12	0.15	0.19	0.21	0.23
6					2000	0.08	0.13	0.15	0.16	0.22
7					10000	0.07	0.07	0.08	0.11	0.13
Summary

Cell Formulas
Range	Formula
C3	C3	=A3*INDEX(F3:J7,MATCH(A3,E3:E7,1),MATCH(B3,F2:J2,1))

This is exactly what I needed. As usual I was trying to over complicate things. Thank you so much Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.