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.

From	To	Mileage
Main	Ditch Road	12.51
The Other Branch	Main	16.86

<tbody>
</tbody><colgroup><col><col><col></colgroup>

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.

	Main	Ditch Road	The Other Branch	Another Brance	Last Branch
Main	0.00	12.51	16.86	0.33	12.01
Ditch Road	12.51	0.00	9.16	12.15	33.96
The Other Branch	16.86	9.16	0.00	16.55	27.59
Another Branch	0.33	12.15	16.55	0.00	12.33
Last Branch	12.01	33.96	27.59	12.33	0.00

<tbody>
</tbody><colgroup><col><col span="5"></colgroup>

 

[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.

	A	B	C
1	From	To	Mileage
2	Main	Ditch Road	12.51
3	The Other Branch	Main	16.86

<tbody>
</tbody>

	A	B	C	D	E	F
10		Main	Ditch Road	The Other Branch	Another Brance	Last Branch
11	Main	0.00	12.51	16.86	0.33	12.01
12	Ditch Road	12.51	0.00	9.16	12.15	33.96
13	The Other Branch	16.86	9.16	0.00	16.55	27.59
14	Another Branch	0.33	12.15	16.55	0.00	12.33
15	Last Branch	12.01	33.96	27.59	12.33	0.00

<tbody>
</tbody>

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.