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

Hi Max

I would start by creating some named ranges using InsertNameDefine.
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))

--
Regards

Roger Govier


"Max" wrote in message
...
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

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
"Roger Govier" wrote:

Hi Max

I would start by creating some named ranges using InsertNameDefine.
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))

--
Regards

Roger Govier


"Max" wrote in message
...
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

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

HTH
--
AP

"Max" a écrit dans le message de news:
...
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
"Roger Govier" wrote:

Hi Max

I would start by creating some named ranges using InsertNameDefine.
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))

--
Regards

Roger Govier


"Max" wrote in message
...
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

Thanks Ardus,

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

Max

"Ardus Petus" wrote:

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

HTH
--
AP

"Max" a écrit dans le message de news:
...
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
"Roger Govier" wrote:

Hi Max

I would start by creating some named ranges using InsertNameDefine.
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))

--
Regards

Roger Govier


"Max" wrote in message
...
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