Need help with a sheet patterned after a multiplication chart

**Mitch_OC** · 06-05-2013, 12:42 PM

I'm a newbie here and probably graded at average to below average when it comes to my excel skills.

My issue is this. I work for a courier company and I'm trying to build a speadsheet that shows the mileage between different cities just as you would with an old multiplication chart. IE, Find city 1 on either the top or side column and follow that row/column until it lines up with city 2. Of course this means that distances for each would be listed twice as each city would be listed in both the top row and the left column.

I'm hoping there is an easy way to create formulas to auto-fill in one half of the grid as opposed to hand-typing everything in twice or building formula's cell by cell to copy the appropriate data. The latter is what I've done in the past and for a 50x50 city grid, it took a while to enter in all the different formulas.

The one I need to build now is 500x500.

I'm hoping somebody out there either knows a way to program this easily/quickly or (shooting for the moon here) already has one I can use.

Thanks.

Mitch

**ChemistB** · 06-05-2013, 01:20 PM

Assuming your cities are in the same order across and down, then you create a diagnol across the center. Fill in the upper right triangle manually and put this formula in B3

=INDEX($B$2:$E$5, MATCH(B$1,$A$2:$A$5,0), MATCH($A3, $B$1:$E$1,0))
You'd then copy that into everything in the lower left triangle.

**Mitch_OC** · 06-05-2013, 02:45 PM

Hi,

Thanks for the quick reply.

I see this works perfectly for a 4x4 grid but nothing bigger.

In your formula...
=INDEX($B$2:$E$5, MATCH(B$1,$A$2:$A$5,0), MATCH($A3, $B$1:$E$1,0))

Is it just the index section "=INDEX($B$2:$E$5" where I would change that to the equivalent of a 500x500 grid?

Correction....My work excel (2003) can only seem go to up to 256 columns. so would the change be "=INDEX($B$2:$IV$257"?

edit after trial and error....

Nope...tried that and it didn't work. What change would I do to expand this to 256x256?

**ChemistB** · 06-05-2013, 03:24 PM

Assuming your cities are in B1:IV1 and in A2:A257
In B3
=INDEX($B$2:$IV$257, MATCH(B$1,$A$2:$A$257,0), MATCH($A3, $B$1:$IV$1,0))
Does that work for you?

**Mitch_OC** · 06-05-2013, 03:55 PM

That works perfectly. Thank you.

I envy your ability to do this sort of thing. I know my life would be so much easier if I had such skills in using excel. It is a wonderfully useful tool.

Mitch