match - Return more than 1 value in Excel table (for calculating total route distance)

Question

I made this Excel sheet to calculate total distance of a particular route and to keep track of trips. However, it makes many calculations which will eventually make it very slow (even more if routes have many stops).

In the image, Column P is the distance between PLACE_1 and PLACE_2, Column Q is the distance between PLACE_2 and PLACE_3 and so on. Column O is the sum of all these distances.

I used =INDEX(distance_table, MATCH(), MATCH()) to find the distances

This is the distance_table (distances are not real, just an example):

So as you can see, it works but it is very inefficient, the index/match formula will be used 10 times per trip.

What could be done to improve it? (considering there could be more than 10 stops per trip)

Thanks in advance!

Answer 1

You can use SUM/INDEX/MATCH/MATCH array formula:

=SUM(IFERROR(INDEX($B$2:$J$10,N(IF(1,MATCH($A$15:$J$15,$A$2:$A$10,0))),N(IF(1,MATCH($B$15:$K$15,$B$1:$J$1,0)))),0))

Array formula after editing is confirmed by pressing ctrl + shift + enter