martrix help

M

memotronic

I have a matrix which contains distance for 2 points , if we match the
by using 2 rulers then we get the distance between these points.
want to create a forumlla that let me get the distance automalical
when I wirte the 2 points or you can call it the source an
destination.

for example if I know the distance between many cities so how can
chose any 2 cities and get the distance between them ?




Thanks for you help in advanc
 
G

Gord Dibben

Example only....adjust ranges and cities.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
Insert>Name>Create, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1

This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP
 
P

Pete

If your table is as in the link you posted, with cities selected in C14
and C15, the following formula in E14 will give you the distance
between the two inputs:

=VLOOKUP(C14,A2:K11,MATCH(C15,A2:A11,0)+1,1)

Change the cell references if they differ.

I would suggest you do a data validation on cells C14 and C15 using the
list in A$2:A$11, so that the user can only select cities within your
table.

Pete
 
P

Pete

In what way did it not work? Did you get #N/A errors or some other
error, or did you get the wrong values?

I set up a table exactly the same as in your picture, and it worked for
me.

Pete
 
Top