Mileage Table Lookup

[Texas Onager]

From a table with names of cities in column a and row 1 and the corresponding mileages between the cities in the field of the table such as that found on state maps as shown in the example below, can Excel be used to lookup mileages to populate cells in a spreadsheet?

A B C B E F
1 DALLAS FTW HSTN WACO
2 DALLAS 0 32 241 96
3 FTW 32 0 273 90
4 HSTN 241 273 0 207
5 WACO 96 90 207 0


Our operator would type HSTN into a cell in the spreadsheet as the origination point of something we need to track. Then WACO would be typed into the destination cell. What we would like to do is have Excel lookup on the table above and return the value 207 to a specific cell.

Is this possible?

[lenze]

Here is an example. use DataValidation to create drop downs for your cities

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows NT 4
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
B11 =
	A	B	C	D	E	F	G	H	I
5	Start	DALLAS
6						DALLAS	FTW	HSTN	WACO
7					DALLAS	0	32	241	96
8	Destination	FTW			FTW	32	0	273	90
9					HSTN	241	273	0	207
10					WACO	96	90	207	0
11	Mileage	32
Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.10] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[Aladin Akyurek]

On 2002-06-04 07:06, Texas Onager wrote:
From a table with names of cities in column a and row 1 and the corresponding mileages between the cities in the field of the table such as that found on state maps as shown in the example below, can Excel be used to lookup mileages to populate cells in a spreadsheet?

A B C B E F
1 DALLAS FTW HSTN WACO
2 DALLAS 0 32 241 96
3 FTW 32 0 273 90
4 HSTN 241 273 0 207
5 WACO 96 90 207 0


Our operator would type HSTN into a cell in the spreadsheet as the origination point of something we need to track. Then WACO would be typed into the destination cell. What we would like to do is have Excel lookup on the table above and return the value 207 to a specific cell.

Is this possible?

Lets say that A1:E5 houses the sample you provided, H1 the origin/source, and H2 the destination.

In H3 enter:

=OFFSET($A$1,MATCH(H2,$A$2:$A$5,0),MATCH(H1,$B$1:$E$1,0))

To repeat, the first MATCH has the destination as lookup value and the second MATCH the source as lookup value.

Aladin

[eliW]

On 2002-06-04 07:06, Texas Onager wrote:
From a table with names of cities in column a and row 1 and the corresponding mileages between the cities in the field of the table such as that found on state maps as shown in the example below, can Excel be used to lookup mileages to populate cells in a spreadsheet?

A B C B E F
1 DALLAS FTW HSTN WACO
2 DALLAS 0 32 241 96
3 FTW 32 0 273 90
4 HSTN 241 273 0 207
5 WACO 96 90 207 0


Our operator would type HSTN into a cell in the spreadsheet as the origination point of something we need to track. Then WACO would be typed into the destination cell. What we would like to do is have Excel lookup on the table above and return the value 207 to a specific cell.

Is this possible?

Hi,
Try this
Eli

Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
G3		=	=VLOOKUP(G2,$A$1:$E$5,MATCH(H2,A1:E1),0)
	A	B	C	D	E	F	G	H
1		DALLAS	FTW	HSTN	WACO		ORIGINATION	DESTINATION
2	DALLAS	0	32	241	96		DALLAS	WACO
3	FTW	32	0	273	90		:alert("=VLOOKUP(G2,$A$1:$E$5,MATCH(H2,A1:E1),0)")>96
4	HSTN	241	273	0	207
5	WACO	96	90	207	0
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo