read Matrix

[Dan]

Hello,

If I have a matrix that looks something like

A
B 0 0.2 0.4 0.6 1
50% 1 2 3 4
70% 5 6 7 8
100 9 10 11 12


I nedd a funtion that will be able to read the matrix somthing like

=MyMatrix(A,B)


if A between o.4 and 0.2 and B between 50 and 70 then the result is 6
If A between o.4 and 0.6 and B between 50 and 70 then the result is 7

I presume I can do it with a lot of if statements but maybe there is a
cleaner and faster way to do it.


Many thanks
Dan

 

[Stefi]

If you arrange your matrix this way:

A B C D E F
1 0% 0 0,2 0,4 0,6 1
2 50% 1 2 3 4
3 70% 5 6 7 8
4 100% 9 10 11 12

then

=INDEX(C2:F4,MATCH(Bvalue,B1:B4,1),MATCH(Avalue,B1:F1,1))

gives the required matrix element.

Regards,
Stefi


„Dan†ezt írta:

 

[Dan]

fantastic, many thanks

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Dan†ezt írta:

 

[Ken Johnson]

If your matrix is in A1:E4, A value in G1 and B value in H1...

=INDEX(B2:E4,4-MATCH(H1,{1,0.7,0.5},-1),5-MATCH(G1,
{1,0.6,0.4,0.2},-1))

Ken Johnson