Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

Use OFFSET(). The column offset may need to be adjusted by one to get the
column number to match.

If you wer look for data in column B (= 2) then your offset from column A is
2 (col B) - 1 = 1

"St03mp" wrote:

Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

Thanks Joel,


I'm not really getting it, since the column number is variable, how can I
ever put in the correct offset?

And to be honest (no offense), I was hoping for a more structural solutions.
Things like OFFSET are a bit wobbly.



"Joel" wrote:

Use OFFSET(). The column offset may need to be adjusted by one to get the
column number to match.

If you wer look for data in column B (= 2) then your offset from column A is
2 (col B) - 1 = 1

"St03mp" wrote:

Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

I think index is better. The code is for a table going from A9:D51. I'm not
sure how many columns you have and what you need to match to pick the
corrrect column.

=index(A9:D51,MATCH(B2;A9:A51;1),MATCH("factor 1";A9:D9;1))


"St03mp" wrote:

Thanks Joel,


I'm not really getting it, since the column number is variable, how can I
ever put in the correct offset?

And to be honest (no offense), I was hoping for a more structural solutions.
Things like OFFSET are a bit wobbly.



"Joel" wrote:

Use OFFSET(). The column offset may need to be adjusted by one to get the
column number to match.

If you wer look for data in column B (= 2) then your offset from column A is
2 (col B) - 1 = 1

"St03mp" wrote:

Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

If you add a header row (maybe in row 8) giving the Length (eg 2500) for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

I was trying that too, but that's not working... Here is why:

INDEX can crosshair a column and row, ending up with 1 cell/value.

your formula:
=index(A9:D51,MATCH(B2;A9:A51;1),MATCH("factor 1";A9:D9;1))

First MATCH: find corresponding row with input value 'Dia'.
lookup_array: first column of the data table. (no problems so far)

Second MATCH: find corresponding column with input value 'Length'.
lookup_array: a 1-row array. The row number of the array is the output of
the first MATCH. The column number array is column B till column D. So, the
lookup_array here is variable, depending on the first MATCH:

xB:xD

And there is the problem: how do I tell this second MATCH which row number
it has to usein its lookup_array?



"Joel" wrote:

I think index is better. The code is for a table going from A9:D51. I'm not
sure how many columns you have and what you need to match to pick the
corrrect column.

=index(A9:D51,MATCH(B2;A9:A51;1),MATCH("factor 1";A9:D9;1))


"St03mp" wrote:

Thanks Joel,


I'm not really getting it, since the column number is variable, how can I
ever put in the correct offset?

And to be honest (no offense), I was hoping for a more structural solutions.
Things like OFFSET are a bit wobbly.



"Joel" wrote:

Use OFFSET(). The column offset may need to be adjusted by one to get the
column number to match.

If you wer look for data in column B (= 2) then your offset from column A is
2 (col B) - 1 = 1

"St03mp" wrote:

Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

Hello Charles,

That is not the case. The header row has the "factor X" values, which are
the end results.
The Lengths are in the data table, and each row has it's own lengths. That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg 2500) for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much more
columns and rows in reality.

Thanks,
St03mp

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column row of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length from the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values, which are
the end results.
The Lengths are in the data table, and each row has it's own lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg 2500) for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has
to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much
more
columns and rows in reality.

Thanks,
St03mp

I can't figure it out....

I'm doing the following:

=OFFSET(A8;MATCH(C4;A9:A51;1);MATCH(C6;B9:D9;1))

This displays the correct length, corresponding to the input dia and length.
How do I tell that cell to go back up X rows.

Or:

When I try to create a range (array) with OFFSET (height=1, width=3) I get
really mixed up results, which make no sence at all... When I set width to 4
or less, I get a #VALUE as result. When I use width=5, I recieve the value
that is in a cell more to the right. But shouldn't I just get an array as
result, like: B14:D14 ?

PS: Joel; I misjudged the effect of OFFSET. I assumed it really replaced the
value of a cell... but that's obvious not the case.


"Charles Williams" wrote:

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column row of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length from the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values, which are
the end results.
The Lengths are in the data table, and each row has it's own lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg 2500) for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it has
to
look in the
row(number) I just looked up with the first MATCH? And how do I point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains much
more
columns and rows in reality.

Thanks,
St03mp

I think you said that your data was

first row : factors
subsequent rows contain a diameter followed by lengths

so step 1 is to find the right row with a MATCH
then step 2 is to use OFFSET so that you get a row of lengths for that row &
diameter
then step 3 is to do a MATCH on that row to find the column that contains
the length
then step 4 is to do an INDEX that looks at the first row and uses the
column from step 3 to get the factor

so if row 8 contains the factors in Cols B:D
and row 9 through 51 contain the diamaeters (col A) and lengths (Col B:D)
and B2 contains the diameter you want to lookup, and C2 contains the length
you want to lookup

then this formula should work
=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))


where
MATCH(B2,$A$9:$A$51,1) corresponds to step 1
OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) corresponds to step 2
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1) corresponds to step 3
INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))
corresponds to step 4

regards

--
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
I can't figure it out....

I'm doing the following:

=OFFSET(A8;MATCH(C4;A9:A51;1);MATCH(C6;B9:D9;1))

This displays the correct length, corresponding to the input dia and
length.
How do I tell that cell to go back up X rows.

Or:

When I try to create a range (array) with OFFSET (height=1, width=3) I get
really mixed up results, which make no sence at all... When I set width to
4
or less, I get a #VALUE as result. When I use width=5, I recieve the value
that is in a cell more to the right. But shouldn't I just get an array as
result, like: B14:D14 ?

PS: Joel; I misjudged the effect of OFFSET. I assumed it really replaced
the
value of a cell... but that's obvious not the case.


"Charles Williams" wrote:

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column row
of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length from
the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values, which
are
the end results.
The Lengths are in the data table, and each row has it's own lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg 2500)
for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the
input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it
has
to
look in the
row(number) I just looked up with the first MATCH? And how do I
point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to
select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains
much
more
columns and rows in reality.

Thanks,
St03mp

Nice one.
Got it finally working now! Thanks!

Now my last issue;

If input Dia =< table Dia, the next greater Dia in that row should be picked.
If input Dia == table Dia, no changes are needed.

It should look something like this:

IF(C6=<'tableDia';column number+1)

Where C6 is the input field for Length.

Besides the problem of getting the correct column number, the formula will
return a #N/A when the input length (C6) is smaller then the smallest length
in the data table...

"Charles Williams" wrote:

I think you said that your data was

first row : factors
subsequent rows contain a diameter followed by lengths

so step 1 is to find the right row with a MATCH
then step 2 is to use OFFSET so that you get a row of lengths for that row &
diameter
then step 3 is to do a MATCH on that row to find the column that contains
the length
then step 4 is to do an INDEX that looks at the first row and uses the
column from step 3 to get the factor

so if row 8 contains the factors in Cols B:D
and row 9 through 51 contain the diamaeters (col A) and lengths (Col B:D)
and B2 contains the diameter you want to lookup, and C2 contains the length
you want to lookup

then this formula should work
=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))


where
MATCH(B2,$A$9:$A$51,1) corresponds to step 1
OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) corresponds to step 2
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1) corresponds to step 3
INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))
corresponds to step 4

regards

--
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
I can't figure it out....

I'm doing the following:

=OFFSET(A8;MATCH(C4;A9:A51;1);MATCH(C6;B9:D9;1))

This displays the correct length, corresponding to the input dia and
length.
How do I tell that cell to go back up X rows.

Or:

When I try to create a range (array) with OFFSET (height=1, width=3) I get
really mixed up results, which make no sence at all... When I set width to
4
or less, I get a #VALUE as result. When I use width=5, I recieve the value
that is in a cell more to the right. But shouldn't I just get an array as
result, like: B14:D14 ?

PS: Joel; I misjudged the effect of OFFSET. I assumed it really replaced
the
value of a cell... but that's obvious not the case.


"Charles Williams" wrote:

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column row
of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length from
the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values, which
are
the end results.
The Lengths are in the data table, and each row has it's own lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg 2500)
for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the
input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it
has
to
look in the
row(number) I just looked up with the first MATCH? And how do I
point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to
select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains
much
more
columns and rows in reality.

Thanks,
St03mp

You need to sort the coumns of data so that the lengths are in descending
order going from left to right.

Then use -1 instead of 1 in step 3

=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,-1))

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Nice one.
Got it finally working now! Thanks!

Now my last issue;

If input Dia =< table Dia, the next greater Dia in that row should be
picked.
If input Dia == table Dia, no changes are needed.

It should look something like this:

IF(C6=<'tableDia';column number+1)

Where C6 is the input field for Length.

Besides the problem of getting the correct column number, the formula will
return a #N/A when the input length (C6) is smaller then the smallest
length
in the data table...

"Charles Williams" wrote:

I think you said that your data was

first row : factors
subsequent rows contain a diameter followed by lengths

so step 1 is to find the right row with a MATCH
then step 2 is to use OFFSET so that you get a row of lengths for that
row &
diameter
then step 3 is to do a MATCH on that row to find the column that contains
the length
then step 4 is to do an INDEX that looks at the first row and uses the
column from step 3 to get the factor

so if row 8 contains the factors in Cols B:D
and row 9 through 51 contain the diamaeters (col A) and lengths (Col B:D)
and B2 contains the diameter you want to lookup, and C2 contains the
length
you want to lookup

then this formula should work
=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))


where
MATCH(B2,$A$9:$A$51,1) corresponds to step 1
OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) corresponds to step 2
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1) corresponds to step
3
INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))
corresponds to step 4

regards

--
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
I can't figure it out....

I'm doing the following:

=OFFSET(A8;MATCH(C4;A9:A51;1);MATCH(C6;B9:D9;1))

This displays the correct length, corresponding to the input dia and
length.
How do I tell that cell to go back up X rows.

Or:

When I try to create a range (array) with OFFSET (height=1, width=3) I
get
really mixed up results, which make no sence at all... When I set width
to
4
or less, I get a #VALUE as result. When I use width=5, I recieve the
value
that is in a cell more to the right. But shouldn't I just get an array
as
result, like: B14:D14 ?

PS: Joel; I misjudged the effect of OFFSET. I assumed it really
replaced
the
value of a cell... but that's obvious not the case.


"Charles Williams" wrote:

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column
row
of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length
from
the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values,
which
are
the end results.
The Lengths are in the data table, and each row has it's own
lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg
2500)
for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the
input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it
has
to
look in the
row(number) I just looked up with the first MATCH? And how do I
point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to
select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains
much
more
columns and rows in reality.

Thanks,
St03mp

Ah, ofcourse! Should have thought of that myself.

Anyway, it's fully working now. Thanks a lot for your input!


Stef

"Charles Williams" wrote:

You need to sort the coumns of data so that the lengths are in descending
order going from left to right.

Then use -1 instead of 1 in step 3

=INDEX($B$8:$D$8, 1, MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,-1))

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Nice one.
Got it finally working now! Thanks!

Now my last issue;

If input Dia =< table Dia, the next greater Dia in that row should be
picked.
If input Dia == table Dia, no changes are needed.

It should look something like this:

IF(C6=<'tableDia';column number+1)

Where C6 is the input field for Length.

Besides the problem of getting the correct column number, the formula will
return a #N/A when the input length (C6) is smaller then the smallest
length
in the data table...

"Charles Williams" wrote:

I think you said that your data was

first row : factors
subsequent rows contain a diameter followed by lengths

so step 1 is to find the right row with a MATCH
then step 2 is to use OFFSET so that you get a row of lengths for that
row &
diameter
then step 3 is to do a MATCH on that row to find the column that contains
the length
then step 4 is to do an INDEX that looks at the first row and uses the
column from step 3 to get the factor

so if row 8 contains the factors in Cols B:D
and row 9 through 51 contain the diamaeters (col A) and lengths (Col B:D)
and B2 contains the diameter you want to lookup, and C2 contains the
length
you want to lookup

then this formula should work
=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))


where
MATCH(B2,$A$9:$A$51,1) corresponds to step 1
OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) corresponds to step 2
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1) corresponds to step
3
INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))
corresponds to step 4

regards

--
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
I can't figure it out....

I'm doing the following:

=OFFSET(A8;MATCH(C4;A9:A51;1);MATCH(C6;B9:D9;1))

This displays the correct length, corresponding to the input dia and
length.
How do I tell that cell to go back up X rows.

Or:

When I try to create a range (array) with OFFSET (height=1, width=3) I
get
really mixed up results, which make no sence at all... When I set width
to
4
or less, I get a #VALUE as result. When I use width=5, I recieve the
value
that is in a cell more to the right. But shouldn't I just get an array
as
result, like: B14:D14 ?

PS: Joel; I misjudged the effect of OFFSET. I assumed it really
replaced
the
value of a cell... but that's obvious not the case.


"Charles Williams" wrote:

OK so you could use OFFSET, something like this:

=OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ... returns the 3 column
row
of
lengths for diameter B2, so something like

=INDEX($B$8:$D$8, 1,
MATCH(C2,OFFSET($B$8,MATCH(B2,$A$9:$A$51,1),0,1,3) ,1))

should return the factor from the appropriate column for the length
from
the
header row B8:D8.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello Charles,

That is not the case. The header row has the "factor X" values,
which
are
the end results.
The Lengths are in the data table, and each row has it's own
lengths.
That's
the difficulty here.

"Charles Williams" wrote:

If you add a header row (maybe in row 8) giving the Length (eg
2500)
for
each column then you can use something like

=INDEX($B$8:$D$51,MATCH(B2,$A$9:$A$51,1),MATCH(C2, $B$8:$D$8,0))

(assumes the length is in C2)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"St03mp" wrote in message
...
Hello,

Is there a way to select a variable lookup_array.

This is the situation.

Input:
Dia: 130 (e.g.)
Length: 2500 (e.g.)

Data:
Dia factor 0 factor 1 factor 2
80 1200 2400 3600
90 1350 2700 4050
100 1500 3000 4500
110 1650 3300 4950
120 1800 3600 5400
130 1950 3900 5850

Result: factor X

I first select the correct row number, using MATCH:
=MATCH(B2;A9:A51;1)

Now I want to look up the corresponding column number, using the
input
value
'length'.
I can do this, using MATCH again. But How do I tell excel that it
has
to
look in the
row(number) I just looked up with the first MATCH? And how do I
point
specifically to the correct array (column range)?

When I have found the correct row and column, I can use INDEX to
select
the
corresponding factor.


PS: IF-THEN solutions are no good, since the data table contains
much
more
columns and rows in reality.

Thanks,
St03mp