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

 

[NBVC]

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

That is the answer I got... see my sample above.

 

[methody]

Thank you that's brilliant

I know I'm pushing it but would there be an easy way of adjusting the formula to find the second longest journey rather than the longest.

Aladin thanks for yours too. It worked perfectly although I am likely to be dealing with a large number of towns so it was going to be easier to use fewer formula

 

[NBVC]

Thank you that's brilliant

I know I'm pushing it but would there be an easy way of adjusting the formula to find the second longest journey rather than the longest.

Try:

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

 

[methody]

Briiliant. Than you very much

 

[NBVC]

Briiliant. Than you very much

You are welcome!

 

[Aladin Akyurek]

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

aaTop 1 MaxAssociates methody.xls
	A	B	C	D	E	F	G
1		bangor	donag	groo	belast	portrush	ballymena
2	bangor		6	2	12	60	30
3	donag	6		4	18	66	66
4	groo	2	4		14	62	32
5	belast	12	18	14		50	25
6	portrush	60	66	62	50		20
7	ballymena	30	66	32	25	20
8
9
10
11	66
12	2
13	donag	ballymena
14	donag	portrush
15
Sheet2

A11:

=MAX(B2:G7)

A12:

=COUNTIF(B2:G7,A11)/2

A13:

=IF(ROWS($B$13:B13)<=A$12,INDEX($A$2:$A$7,SMALL(IF($B$2:$G$7=A$11,ROW($B$2:$G$7)-ROW($B$2)+1),ROWS($B$13:B13))),"")

Confirmed with control+shift+enter and copied down.

B13:

=IF(ROWS($B$13:B13)<=A$12,INDEX($B$1:$G$1,LARGE(IF($B$2:$G$7=A$11,COLUMN($B$2:$G$7)-COLUMN($B$2)+1),ROWS($B$13:B13))),"")

Confirmed with control+shift+enter and copied down.

 

[methody]

Hello Aladin

I don' want to cause offence to NBVC but think I'm coming round to your way because it seems to deal with the poossibility of there being two distances the same. Although how woud I deal with the second highest? Would I have to adjust the LARGE element of the formula?

 

[methody]

I'm becoming a bit of a pest. I've post myexample. In the example there are two journeys of 67 miles -donag to armagh and donag to portrush. The value in cell E19 should be 'donag'. Can you see why it isn't

Copy of fixtrial2.xls
	D	E	F	G	H	I	J	K	L	M
5	x	x	bangor	donag	groo	belast	portrush	ballymena	armagh
6	bangor			6	2	12	60	30	56
7	donag		6		4	18	67	36	62
8	groo		2	4		14	62	32	59
9	belast		12	18	14		50	25	45
10	portrush		60	67	62	50		20	67
11	ballymena		30	36	32	25	20		45
12	armagh		56	62	59	45	67	45
13
14
15
16	67
17	2
18	donag	armagh
19	portrush	portrush
a2

 

[Aladin Akyurek]

I'm becoming a bit of a pest. I've post myexample. In the example there are two journeys of 67 miles -donag to armagh and donag to portrush. The value in cell E19 should be 'donag'. Can you see why it isn't

...

Not your fault. The setup is simply not good enough. The remedy is to leave empty the cells below the diagonal. Can we go with that?

 

[Aladin Akyurek]

I'm becoming a bit of a pest. I've post myexample. In the example there are two journeys of 67 miles -donag to armagh and donag to portrush. The value in cell E19 should be 'donag'. Can you see why it isn't
...

If you leave below diagonal empty...

Copy of fixtrial2 methody.xls
	D	E	F	G	H	I	J	K	L	M
5	x	x	bangor	donag	groo	belast	portrush	ballymena	armagh
6	bangor			6	2	12	60	30	56
7	donag				4	18	67	36	67
8	groo					14	62	32	59
9	belast						50	25	45
10	portrush							20	67
11	ballymena								45
12	armagh
13
14
15
16	67
17	3
18	donag	armagh
19	donag	portrush
20	portrush	armagh
21
a2

D16:

=MAX(F6:M14)

D17:

=COUNTIF(F6:M14,D16)

D18:

=IF(ROWS(D$18:D18)<=D$17,INDEX($D$6:$D$14,SMALL(IF($F$6:$M$14=D$16,ROW($F$6:$M$14)-ROW($F$6)+1),ROWS(E$18:E18))),"")

confirmed with control+shift+enter and copied down.

E18:

=IF(ROWS(E$18:E18)<=D$17,INDEX($F$5:$M$5,LARGE(IF($F$6:$M$14=D$16,IF($D$6:$D$14=$D18,COLUMN($F$6:$M$14)-COLUMN($F$6)+1)),COUNTIF($D$18:D18,D18))),"")

confirmed with control+shift+enter and copied down.