Lookup in a matrix (with x and y headings) based on value x and y

[johan]

Somebody can help me out.

In a spreadsheet I have column-A with x values and column-B with y
values.
In column-C I want the result that comes out of a matrix based on the
values from A and B.

Example;

Column-A Column-B Column-C Column-X
Y Z AA AB
row-1 temp: day:
matrixvalue: 0 1 2 3 4
row-2 20 1
f 10 a c d
e
row-3 10 3
d 20 f g h i
row-4 30 4
m 30 j k l m

In column C I need the formule that takes the value in column A for
lookup in column X and the value in column B for lookup in row one of
column Y-Z-AA-AB. De result of the horizontal/vertical is the letter
in column C.
Another issue is that if the value in column A is not the same as the
values in column X, then the formula has to look at the nearest by but
higher value. For example: value is 14 then you have to take 20.

Hopely some help from smart people.

regards,
Johan

 

[Lars-Åke Aspelin]

Somebody can help me out.

In a spreadsheet I have column-A with x values and column-B with y
values.
In column-C I want the result that comes out of a matrix based on the
values from A and B.

Example;

Column-A Column-B Column-C Column-X
Y Z AA AB
row-1 temp: day:
matrixvalue: 0 1 2 3 4
row-2 20 1
f 10 a c d
e
row-3 10 3
d 20 f g h i
row-4 30 4
m 30 j k l m

In column C I need the formule that takes the value in column A for
lookup in column X and the value in column B for lookup in row one of
column Y-Z-AA-AB. De result of the horizontal/vertical is the letter
in column C.
Another issue is that if the value in column A is not the same as the
values in column X, then the formula has to look at the nearest by but
higher value. For example: value is 14 then you have to take 20.

Hopely some help from smart people.

regards,
Johan

Assuming that values in column B always match with the row 1 values of
the matrix and that values in column A is never larger than the
largest value on the column X values of the matrix.

Try the following formula in cell C2:

=INDEX(X$2:AB$4,IF(A2<X$2,1,MATCH(A2,X$2:X$4)+ISNA(MATCH(A2,X$2:X$4,0))),MATCH(B2,X$1:AB$1))

Copy down in column C as far as you have data in columns A and B.

The IF A2<X$2 part of the formula takes care of temp values less than
the lowest value in column X

MATCH finds the "nearest but lower value", so the ISNA() part of the
formula takes care of the "nearest bit higher value" requirement.

Hope this helps / Lars-Åke

 

[johan]

Thanks a lot.
It works just as required at this moment.


regards, Johan.