# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  Pull info from table of distances

## JMPro

Hello, 

I have a table of distances between 500 locations (title of columns are the same as title of rows).

With min() function, I'm able to find the shortest distance, but I want to also display which city (title of columns) is the nearest for each of them. Is there a formula that can provide me that answer ?

Also, I'm able to count the number of cities that are  in a radius of 1000 km of each location, but would like to list the cities that respond to this criteria. Again, I thought that pivot tables, with the help of filters, would help me to achieve this task but haven't figured out yet.

Trying to stay away form VBA & macros if possible !!

Any help that you could provide ? Fictionnal data attached.

----------


## Glenn Kennedy

The first bit is this:

=INDEX($F$1:$N$1,MATCH(C2,F2:N2,0))

copied down.

----------


## Glenn Kennedy

When you say "list in another tab", exactly what do you mean?  What do you want to see?  Where do you want to see it?

If you want multiple city names in E2, etc, then:


1.  Can I use VBA?

2.  If not can I use a helper column?

3.  If not what is the MAXIMUM number of cities <100 km in your real data thast would need to be concatenated into a single cell (more than about 6 and option 3 isn't really a runner.

But if you want Toronto in a2 in another sheet/place and the cities <100 km in the column(s) next to A2, that's totally do-able.

----------


## Pete_UK

If you are still using XL2016, as your profile states, you can use this array* formula in B2:

=INDEX($F$1:$N$1,MATCH(MIN(IF(F2:N2>0,F2:N2,10000)),F2:N2,0))

**Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, rather than the usual Enter.*

Copy down as required.

Hope this helps.

Pete

----------


## MarvinP

HI JMPro and welcome to the forum,

See the attached where the formula in Column B is a Control+Shift+Enter formula.  I also converted your table to another format so you could simply use a distance drop down to filter like you desire.

Distances between cities.xlsx

----------


## JMPro

Glenn wrote:

Re: Pull info from table of distances
The first bit is this:

=INDEX($F$1:$N$1,MATCH(C2,F2:N2,0))

copied down.
----

AMAZING ! You're the best ... exactly what i wanted. Many thanks  :Smilie:

----------


## JMPro

> HI JMPro and welcome to the forum,
> 
> See the attached where the formula in Column B is a Control+Shift+Enter formula.  I also converted your table to another format so you could simply use a distance drop down to filter like you desire.
> 
> Attachment 769691



Hi Marvin, 

You may have the simplest & most efficient pivot table for what I was looking to get. How can I replicate that conversion (from colomn, to column & distance column) with my 500 x 500 file ?


Thanks all for the support !

JM :Cool:

----------


## Glenn Kennedy

For a full formula-based solution:

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($F$2:$N$2)/(($F2:$N2> 0)*($F2:$N2< 1000)),COLUMNS($G13:G13))),"")

see file.

----------


## MarvinP

Hi,

There is a tool in Power Query called UnPivot that I converted your data to mine.  It took about 30 seconds to do the conversion and I think you have this tool in 2016 version of Excel.  
Read about it at: https://support.microsoft.com/en-us/...5-f588221c7098 
It isn't that hard if you learn just a tad of Power Query.

Before Power Query I had long VBA that did the same thing.  I'm really glad PQ added it into their toolkit.

----------


## JMPro

Power Query and Unpivot are exactly what I needed here.

Mucho gracias !! Case resolved on my end.

JM

----------

