Most likely Lookup -formula problem

[John Doe]

Hello there,

sorry for quite uninformal subject...

anyways, the problem as follows:

I want to make excel sheet , which has information about distances between
(for example) cities;
(I hope you can figure out the sheet, eventhough the lay-out will most
likely change because of e-mail readers settings...)


A B C D
City_1 City_2 City_3
1 | City 1 0 20 50
2 | City_2 20 0 80
3 | City_3 50 80 0

For example;

distance between City_1 (A1) and City_3 (D1) is 50

so, the formula should be able to find matching value, if I choose from
drop-down list first starting point = City_1 and then the ending point from
next drop-down list = City_3

first formula should find matching value from column A (from row 1) and then
next matching value; City_3 (from column D) and after that to "match these
information and return value from D1

.... clear enough, hope you can understand, ask more, if needed.

Thanks in advance.

Btw, is there some newsgroup, where I could post example excel file ?

 

[Frank Kabel]

Hi
see Excel.worksheet.functions

 

[thomach]

If your table of distances is in cells A58 (the city names in A6:A8
and B55 as row and column headers) and you have your start city in
cell A2 and destination city in cell B2, then use:

=VLOOKUP(A2,A68,MATCH(B2,A55,0),0)

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[wiseman]

I think lookup and match would do what you need, see these pages
http://www.mrexcel.com/tip021.shtml
http://www.excel-vba.com/e-formula-index-match.htm
http://www.cpearson.com/excel/lookups.htm (and this has a worksheet you
can download with examples)
Barbara

 

[RagDyeR]

Debra Dalgleish has a web page that deals with double drop down lists
created in data validation.

Try this link:

http://www.contextures.com/xlDataVal02.html

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello there,

sorry for quite uninformal subject...

anyways, the problem as follows:

I want to make excel sheet , which has information about distances between
(for example) cities;
(I hope you can figure out the sheet, eventhough the lay-out will most
likely change because of e-mail readers settings...)


A B C D
City_1 City_2 City_3
1 | City 1 0 20 50
2 | City_2 20 0 80
3 | City_3 50 80 0

For example;

distance between City_1 (A1) and City_3 (D1) is 50

so, the formula should be able to find matching value, if I choose from
drop-down list first starting point = City_1 and then the ending point from
next drop-down list = City_3

first formula should find matching value from column A (from row 1) and then
next matching value; City_3 (from column D) and after that to "match these
information and return value from D1

.... clear enough, hope you can understand, ask more, if needed.

Thanks in advance.

Btw, is there some newsgroup, where I could post example excel file ?

 

[Gord Dibben]

John

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

 

[CLR]

And yet another.............

Insert a new Row 2
in B2 put 2
in C2 put 3
in d2 put 4
these numbers will be used as reference numbers in the HLOOKUP portion of
the below formula

now in F1, type your "from" city name, and in F2 type your "to" city name

Then in any open cell, (I used G4), put this formula..........and it will do
the "matrix lookup" todisplay mileage between your two cities.......

=VLOOKUP(F1,A26,HLOOKUP(F2,A12,2,FALSE),FALSE)


Vaya con Dios,
Chuck, CABGx3