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

user_11959621 0
Viewed 44

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

enter image description here

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

enter image description here

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!

match distance lookup excel
2 Answers

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

enter image description here

user_121888610

Not intended as an answer. because in terms of performance index/match is faster, just as an alternative and maybe it can give you ideas:

Setup: Setup

Formula:

=SUMPRODUCT(($B$6:$B$14=B3)*($C$5:$K$5=C3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=C3)*($C$5:$K$5=D3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=D3)*($C$5:$K$5=E3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=E3)*($C$5:$K$5=F3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=F3)*($C$5:$K$5=G3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=G3)*($C$5:$K$5=H3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=H3)*($C$5:$K$5=I3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=I3)*($C$5:$K$5=J3);$C$6:$K$14) +
SUMPRODUCT(($B$6:$B$14=J3)*($C$5:$K$5=K3);$C$6:$K$14)
user_15215790
Related