V/H Lookup or INDEX

[jake424]

I'm trying to look up rates based off of weight and miles.

	Weight
Miles	500	1000	1500	2000
250	4.5	6.5	8.5	10.5
500	7.5	9.5	11.5	13.5
750	12.5	14.5	16.5	18.5

<tbody>
</tbody>

I have a cell where I type in the weight and another cell where I type in the miles. I want the 3rd table to be the rate

Weight	675
Miles	300
Rate	XX

<tbody>
</tbody>

I know the answer should be 4.5, but not sure how to look it up. Do I use vlookup, hlookup, INDEX, or a combination?


Thanks!

 

[Marcílio_Lobão]

jake424, Good afternoon.

Your scenario:

	A	B	C	D	E
1		Weight
2	Miles	500	1000	1500	2000
3	250	4.5	6.5	8.5	10.5
4	500	7.5	9.5	11.5	13.5
5	750	12.5	14.5	16.5	18.5

<tbody>
</tbody>

G2 --> WEIGHT --> 675

G3 --> MILES ----> 300

G4 --> FORMULA --> =INDEX($B$3:$E$5, MATCH(G3, $A$3:$A$5, 1), MATCH(G2, $B$2:$E$2, 1))

Is that what you want?
I hope it helps.

 

[Chihiro]

Assuming Miles in A3:A5, Weight in B2:E2 and data table in B3:E5

I'd do something like...
=INDEX(B3:E5,MATCH(LOOKUP(C9,A3:A5),A3:A5),MATCH(LOOKUP(C8,B2:E2),B2:E2))