# index match miles

#### methody

##### Well-known Member
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
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
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
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

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
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

ADVERTISEMENT

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

#### Aladin Akyurek

##### MrExcel MVP
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
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
Not quite it yet
The answer on my example should be
portrush donaghadee

Replies
7
Views
301
Replies
2
Views
97
Replies
9
Views
194
Replies
3
Views
166
Replies
13
Views
199

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

### 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