Need Matrix Lookup Formula

[Guest]

I have a work project that I need help with.

If you are familiar with the mileage distance charts which most all road maps have, you'll know that in order to find the mileage from point A to B you locate the starting place on the left column and move right to find the ending place. Where these two points intersect will have the mileage between the 2 points.

I have a similar matrix in excel and need a formula to lookup the distances for multiple locations. (e.g. Tampa to Jacksonville to Atlanta to Hartford)

I would like to type in the cities and have each distance from point to point to be calculated and then sum up the totals. Most trips will have 5 or less travel points.

 

[Bernie Deitrick]

Charlie,

Let's say your table is in A1:J10, and the two cities names are in D20 and
E20:
=INDEX($A$1:$J$10,MATCH(D20,1:1,FALSE),MATCH(E20,A:A,FALSE))
will return the mileage.

Copy the formula down for as many pairs of cities (two cities for each leg
of the journey) that you need, and sum to get the final.

HTH,
Bernie
MS Excel MVP

I have a work project that I need help with.

If you are familiar with the mileage distance charts which most all road

maps have, you'll know that in order to find the mileage from point A to B
you locate the starting place on the left column and move right to find the
ending place. Where these two points intersect will have the mileage
between the 2 points.

I have a similar matrix in excel and need a formula to lookup the

distances for multiple locations. (e.g. Tampa to Jacksonville to Atlanta
to Hartford)

I would like to type in the cities and have each distance from point to

point to be calculated and then sum up the totals. Most trips will have 5
or less travel points.

 

[Bernie Deitrick]

Charlie,

I should have used more absolute addressing:

=INDEX($A$1:$J$10,MATCH(D20,$1:$1,FALSE),MATCH(E20,$A:$A,FALSE))

to make the formula more copy-friendly...

HTH,
Bernie
MS Excel MVP