Mileage Chart (I'm a beginner)

[Guest]

I want to able to type a TO and FROM destination on my travel form and extract the mileage information from the mileage chart of another worksheet. The mileage chart has the top row naming the cities. The same cities are also listed on lefthand side column, with the mileage between each city in the cells. How can I do this?

 

[Juan Pablo Gonzalez]

If your mileage "chart" is in say, Sheet2, in A1:F6, and your TO and FROM
ranges are in Sheet1, A1 and A2 respectivly, try this:

=INDEX(Sheet2!$A$2:$F$6, MATCH(Sheet1!$A$1, Sheet2!$B$1:$F$1, 0),
MATCH(Sheet1!$A$2, Sheet2!$A$2:$A$6, 0)

or

=VLOOKUP(Sheet1!$A$1, Sheet2!$A$2:$F$6, MATCH(Sheet1!$A$2, Sheet2!$A$1:$F$1,
0), 0)

--
Regards,

Juan Pablo González

I want to able to type a TO and FROM destination on my travel form and

extract the mileage information from the mileage chart of another worksheet.
The mileage chart has the top row naming the cities. The same cities are
also listed on lefthand side column, with the mileage between each city in
the cells. How can I do this?

 

[Bob Phillips]

Assuming that the mileages are in G1:J10, including headings

=INDEX(H2:J4,MATCH("Toronto",G2:G4,0),MATCH("Quebec",H1:J1,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I want to able to type a TO and FROM destination on my travel form and

extract the mileage information from the mileage chart of another worksheet.
The mileage chart has the top row naming the cities. The same cities are
also listed on lefthand side column, with the mileage between each city in
the cells. How can I do this?

 

[Gord Dibben]

Tony

One more method.......

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

 

[Guest]

After I did the Insert>Name>Create, and top row, left column....Excel asks me is I want to replace the existing definition the cities that are listed. What do I do? Options are: Yes,No,Cancel I cancelled just to make sure I didn't mess anything up. I need more help.

 

[Gord Dibben]

Tony

Since you're doing this on a copy of the worksheet or workbook, it won't
matter if you "mess up". Right?

Click "Yes"(on the copied worksheet) and see what happens.

The cells in question may have been part of a named range or had a label.

If you are set up per my example, you should be good to go.

Gord