Lookup value at intersection of two locations in mileage chart

KenGranger

New Member
Joined
Feb 23, 2018
Messages
2
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]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top