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