Possible VLOOKUP formula with two items of dependant data?

cmclaren1978 · Newbie Joined: 02 Jul 2012 Posts: 4

Hi all

I have a list of places, with distances between them all, for example:

Column A - Column B - Column C
A-B 4km
A-C 6km
A-D 8km
B-C 3km
B-D 5km
C-D 1km
.
.
and so on (there are about 20 different locations)

This is sat on a separate tab (Runs), and my working spreadsheet is called (Route)

I need to try and find the quickest route - so trying to plan it using Calc - is there a way of saying if I wanted to go A-C-D-B, and listed that on my Route spreadsheet, I could use a VLOOKUP formula to show the distance? Alternatively, can anyone think of a workaround - possibly using CONCATENATE to show the data as one cell?

Thanks

Robert Tucker · Posted: Fri Jul 13, 2012 2:45 am Post subject:

You have already found:

http://www.yournavigation.org/
https://github.com/openplans/OpenTripPlanner/wiki/
http://bmander.github.com/graphserver/

and had a look at Dijkstra's algorithm and multinode route planning?

In your example, you want to add the 3 values in column C which correspond to there being an A in column A and a C in column B; a C in column A and a D in column B; and a D in column A and a B in column B?
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)

keme · Posted: Fri Jul 13, 2012 5:50 am Post subject:

Calc can't work directly with lookups matching on two fields. You need a workaround, probably merging the field values somehow.

Instead, how about ...