index match miles

[methody]

Hello there
I have a table with Town A, Town B, town C etc down the left hand column and the same towns along the top row. Within the table I have entered the distance from one town to the other. I am looking for the biggest distance and I use MAX. But now want to find the names of the towns which are furthest apart.
I am playing about with index match but I can't get it

 

[Aladin Akyurek]

Hello there
I have a table with Town A, Town B, town C etc down the left hand column and the same towns along the top row. Within the table I have entered the distance from one town to the other. I am looking for the biggest distance and I use MAX. But now want to find the names of the towns which are furthest apart.
I am playing about with index match but I can't get it

Since you didn't provide a sample to work with, I had to cook up one...

aaTop 1 MaxAssociates methody.xls
	A	B	C	D	E
2	A	0	40	40	34
3	B	40	0	60	45
4	C	40	60	0	38
5	D	34	45	38	0
6
7
8
9	A	B	C	D
10	1	2	3	4
11	40	60	60	45
12	2	1	1	1
13	B	C	B	B
14	C
15
Sheet1

A10:

=MATCH(A9,$A$2:$A$5,0)

A11:

=MAX(INDEX($B$2:$E$5,A$10,0))

A12:

=COUNTIF(INDEX($B$2:$E$5,A$10,0),A$11)

A13:

=IF(ROWS($A$13:A13)<=A$12,INDEX($B$1:$E$1,1,SMALL(IF(INDEX($B$2:$E$5,A$10,0)=A$11,COLUMN($B$2:$E$5)-COLUMN($B$2)+1),ROWS($A$13:A13))),"")

which is confirmed with control+shift+enter (not just with enter) and copied down.

Finally, A10:A14 is copied across.

 

[methody]

Thankk very much. For some reason I can't view the example. It is just a massive of html. Do I have to do something to view it correctly or is it a setting on my computer

 

[Aladin Akyurek]

Thankk very much. For some reason I can't view the example. It is just a massive of html. Do I have to do something to view it correctly or is it a setting on my computer

Enable HTML in your board profile.

 

[NBVC]

Like this?

Book1
	A	B	C	D	E	F	G	H	I
1		Town A	Town B	Town C		Max Distances
2	Town A	0	3000	2400		Start	End	Miles
3	Town B	3000	0	2800		Town B	Town A	3000
4	Town C	2400	2800	0		Town A	Town B	3000
5
Sheet1

Formula in F3: =INDEX($B$1:$D$1,MAX((ISNUMBER(SEARCH(MAX($B$2:$D$4),$B$2:$D$4)))*(COLUMN($B$2:$D$4)-1)))

Formula in G3: =INDEX($A$2:$A$4,MIN(IF((ISNUMBER(SEARCH(MAX($B$2:$D$4),$B$2:$D$4)))*(ROW($B$2:$D$4)-1)>0,(ISNUMBER(SEARCH(MAX($B$2:$D$4),$B$2:$D$4)))*(ROW($B$2:$D$4)-1))))

Adjust ranges to suit.

These formulas are confirmed with CTRL+SHIFT+ENTER not just ENTER.

The formulas are reversed in F4 and G4 to give both directions.

 

[methody]

NBVC
That is exactly the result I am looking for I just cannot get them to work. I have adjusted the ranges and entered them as arrays but it just won't work. I realise you've already spent a lot of time on this but can you think of anything

 

[methody]

Here is my example

Book2
	D	E	F	G	H	I	J	K	L
3
4		bangor	donag	groo	belast	portrush	ballymena		#REF!
5	bangor		6	2	12	60	30
6	donag	6		4	18	66	36
7	groo	2	4		14	62	32
8	belast	12	18	14		50	25
9	portrush	60	66	62	50		20
10	ballymena	30	36	32	25	20
Sheet1

 

[Aladin Akyurek]

Here is my example

Book2
	D	E	F	G	H	I	J	K	L
3
4		bangor	donag	groo	belast	portrush	ballymena		#REF!
5	bangor		6	2	12	60	30
6	donag	6		4	18	66	36
7	groo	2	4		14	62	32
8	belast	12	18	14		50	25
9	portrush	60	66	62	50		20
10	ballymena	30	36	32	25	20
Sheet1

Why do you dismiss the setup I forwarded?

 

[NBVC]

NBVC
That is exactly the result I am looking for I just cannot get them to work. I have adjusted the ranges and entered them as arrays but it just won't work. I realise you've already spent a lot of time on this but can you think of anything

I guess I assumed you were starting in Column A....

Try replacing with this formula:

=INDEX(E4:J4,MAX((ISNUMBER(SEARCH(MAX(E5:J10),E5:J10)))*(COLUMN(E5:J10)-COLUMN($D$5))))

And the End Town formula would then be (if you need it):

=INDEX(D5:D10,MIN(IF((ISNUMBER(SEARCH(MAX(E5:J10),E5:J10)))*(ROW(E5:J10)-1)>0,(ISNUMBER(SEARCH(MAX(E5:J10),E5:J10)))*(ROW(E5:J10)-ROW($D$4)))))

Book1
	D	E	F	G	H	I	J	K	L
4		bangor	donag	groo	belast	portrush	ballymena		portrush
5	bangor		6	2	12	60	30		donag
6	donag	6		4	18	66	36
7	groo	2	4		14	62	32
8	belast	12	18	14		50	25
9	portrush	60	66	62	50		20
10	ballymena	30	36	32	25	20
Sheet1

 

[methody]

Not quite it yet
The answer on my example should be
portrush donaghadee