index match miles

methody

Well-known Member
Joined
Jun 17, 2002
Messages
847
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDE
2A0404034
3B4006045
4C4060038
5D3445380
6
7
8
9ABCD
101234
1140606045
122111
13BCBB
14C   
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

Well-known Member
Joined
Jun 17, 2002
Messages
847
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Like this?
Book1
ABCDEFGHI
1Town ATown BTown CMax Distances
2Town A030002400StartEndMiles
3Town B300002800Town BTown A3000
4Town C240028000Town ATown B3000
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

Well-known Member
Joined
Jun 17, 2002
Messages
847
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

Well-known Member
Joined
Jun 17, 2002
Messages
847

ADVERTISEMENT

Here is my example
Book2
DEFGHIJKL
3
4bangordonaggroobelastportrushballymena#REF!
5bangor62126030
6donag64186636
7groo24146232
8belast1218145025
9portrush6066625020
10ballymena3036322520
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Here is my example
Book2
DEFGHIJKL
3
4bangordonaggroobelastportrushballymena#REF!
5bangor62126030
6donag64186636
7groo24146232
8belast1218145025
9portrush6066625020
10ballymena3036322520
Sheet1

Why do you dismiss the setup I forwarded?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
DEFGHIJKL
4bangordonaggroobelastportrushballymenaportrush
5bangor62126030donag
6donag64186636
7groo24146232
8belast1218145025
9portrush6066625020
10ballymena3036322520
Sheet1
 

methody

Well-known Member
Joined
Jun 17, 2002
Messages
847
Not quite it yet
The answer on my example should be
portrush donaghadee
 

Forum statistics

Threads
1,141,596
Messages
5,707,300
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top