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