V/H Lookup or INDEX

[jake424]

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


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Weight[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Miles[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]250
[/TD]
[TD]4.5[/TD]
[TD]6.5[/TD]
[TD]8.5[/TD]
[TD]10.5[/TD]
[/TR]
[TR]
[TD]500
[/TD]
[TD]7.5[/TD]
[TD]9.5[/TD]
[TD]11.5[/TD]
[TD]13.5[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]12.5[/TD]
[TD]14.5[/TD]
[TD]16.5[/TD]
[TD]18.5[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight[/TD]
[TD]675[/TD]
[/TR]
[TR]
[TD]Miles[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Rate[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Weight[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Miles[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]2000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: center"]6.5[/TD]
[TD="align: center"]8.5[/TD]
[TD="align: center"]10.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]7.5[/TD]
[TD="align: center"]9.5[/TD]
[TD="align: center"]11.5[/TD]
[TD="align: center"]13.5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]750[/TD]
[TD="align: center"]12.5[/TD]
[TD="align: center"]14.5[/TD]
[TD="align: center"]16.5[/TD]
[TD="align: center"]18.5[/TD]
[/TR]
</tbody>[/TABLE]

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))