Returning a distance from a tabel, given "from" and "To"?

[Max]

I cannot find a way to return (for instance) the distance in the 3rd column
of a distance table, for a whole series of "From" - "To" iternies in another
tabel. These "from"and "to" fields are text-values in 2 different columns.
For each combination, I want to test whether there is a distance in the table
for this iterny, and if so, return that distance.

The VLookup can only check 1 criteria (or at least I can..) and there might
also be multiple (but unknown numbers) destinations from 1 origin.

I thought of making one text-string out of the "to" and "from" and VLookup
that string, but I imagine there is a more elegant solution to this!

Regards and thanks in advance!

Max

 

[Roger Govier]

Hi Max

I would start by creating some named ranges using Insert>Name>Define.
Firstly create a range called Table, encompassing the whole of your data
set.
Then create a range called To using the top row of the table, and a
range called From using the first column of the table.

Then with the name of the Source in A1 and the Destination in A2, in
cell A3 enter
=INDEX(Table,MATCH(A1,From,0),MATCH(A2,To,0))

 

[Max]

Hi Roger,

thanks for your answer, but I still have a problem: my distance table is not
in the proper form, but it is in the following shape: (just a small piece
from it)

Geel Hanau 628
Geel Trier 669
Geel Zelzate 121
Gent Worms 679
Gent Würzburg 873
Gent Zwolle 328

This shape is nescessary, since the number of destinations (or sources) is
too big to fit in a normal Matrix-form (cause of the max. # of columns) Sorry
I didn't tell this at the first time!

The problem thus is that I have to find ALL the rows containg "Gent"
(example) and then check whether a cell in the second column contains "Worms"
(example). The list is uniquely filtered, so there can only be one match.

Hope you can help me with this problem as well!

Regards,

Max

 

[Ardus Petus]

Try:
=SUMPRODUCT((A1:A999="Gent")*(BA:B999="Worms")*C1:C999)

HTH

 

[Max]

Thanks Ardus,

sometimes it turns out to be much easier than you think...

Max