Lookup value at intersection of two locations in mileage chart

[KenGranger]

I have an expense form that includes a mileage chart. I have "From Branch" and "To Branch" columns, each of which uses the same list of branches as data validation.

[TABLE="width: 332"]
<tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Mileage[/TD]
[/TR]
[TR]
[TD]Main[/TD]
[TD]Ditch Road[/TD]
[TD] 12.51[/TD]
[/TR]
[TR]
[TD]The Other Branch[/TD]
[TD]Main[/TD]
[TD]16.86[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

I have a mileage chart matrix showing approved mileage between the bank branches. I want to use the values in the From and To columns to populate the approved mileage from the following matrix. Assume "Main" in the vertical list is cell A2, and "Main" in the horizontal list is cell B1. The mileage entered in the rows above should be the values shown in bold below.

I'm a relative novice with Excel, and this has be stymied. Any suggestions would be appreciated. I've looked around here a bit and haven't found exactly what I'm looking for. If this has already been answered, please accept my apologies and show me the path of enlightenment. Thanks.
[TABLE="width: 360"]
<tbody>[TR]
[TD] [/TD]
[TD]Main[/TD]
[TD]Ditch Road[/TD]
[TD]The Other Branch[/TD]
[TD]Another Brance[/TD]
[TD]Last Branch[/TD]
[/TR]
[TR]
[TD]Main[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.51[/TD]
[TD="align: right"]16.86[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]12.01[/TD]
[/TR]
[TR]
[TD]Ditch Road[/TD]
[TD="align: right"]12.51[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]9.16[/TD]
[TD="align: right"]12.15[/TD]
[TD="align: right"]33.96[/TD]
[/TR]
[TR]
[TD]The Other Branch[/TD]
[TD="align: right"]16.86[/TD]
[TD="align: right"]9.16[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]16.55[/TD]
[TD="align: right"]27.59[/TD]
[/TR]
[TR]
[TD]Another Branch[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]12.15[/TD]
[TD="align: right"]16.55[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.33[/TD]
[/TR]
[TR]
[TD]Last Branch[/TD]
[TD="align: right"]12.01[/TD]
[TD="align: right"]33.96[/TD]
[TD="align: right"]27.59[/TD]
[TD="align: right"]12.33[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody><colgroup><col><col span="5"></colgroup>[/TABLE]

 

[Marcílio_Lobão]

KenGranger, Good afternoon.

This is a classical case of Cross Reference Search.

Solved by a combination of MATCH and INDEX functions.

[TABLE="class: grid, width: 332"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Mileage[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Main[/TD]
[TD]Ditch Road[/TD]
[TD] 12.51[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]The Other Branch[/TD]
[TD]Main[/TD]
[TD]16.86[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<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]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]Main[/TD]
[TD]Ditch Road[/TD]
[TD]The Other Branch[/TD]
[TD]Another Brance[/TD]
[TD]Last Branch[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Main[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.51[/TD]
[TD="align: right"]16.86[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]12.01[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Ditch Road[/TD]
[TD="align: right"]12.51[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]9.16[/TD]
[TD="align: right"]12.15[/TD]
[TD="align: right"]33.96[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]The Other Branch[/TD]
[TD="align: right"]16.86[/TD]
[TD="align: right"]9.16[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]16.55[/TD]
[TD="align: right"]27.59[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Another Branch[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]12.15[/TD]
[TD="align: right"]16.55[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.33[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Last Branch[/TD]
[TD="align: right"]12.01[/TD]
[TD="align: right"]33.96[/TD]
[TD="align: right"]27.59[/TD]
[TD="align: right"]12.33[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Try to use:

C2 --> =INDEX($B$11:$F$15,MATCH(A2,$A$11:$A$15,0),MATCH(B2,$B$10:$F$10;0))

Is that what you want?

I hope it helps.

 

[KenGranger]

This is exactly what I was looking for. Thanks, Marcilio. I have a lot to learn!

 

[Marcílio_Lobão]

KenGranger, Good afternoon.

All of us are always learning new things in our day-by-day lives.

Glad to have helped you.

Thanks for the feedback.