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

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