# Formula for a Matrix

Hiya

I was wondering what formula would be useful for a matrix (please see attached).  For example, if the likelyhood is rare, and the consequence is insignificant then the answer would be L (cell E42).

Would anyone have a suggestion of the best formula to use?

Thanks.

Greg
Test.xls
###### Who is Participating?

Commented:
If in the spreadsheet you have a list with the varibale combos
ie. Rows 11 and 12 below
A                       B                       C
Likelihood      Consequence
Rare                       Insignificant        L

Then in C12 the formula reads
=INDEX(\$D\$6:\$H\$10;MATCH(B12;\$C\$6:\$C\$10;0);MATCH(C12;\$D\$5:\$H\$5;0))

See attached sample file
Test-sample.xls
0

EngineerCommented:
=INDEX(D6:H10,MATCH("Rare",\$C\$6:\$C\$10,0),MATCH("Insignificant",\$D\$5:\$H\$5,0))
0

EngineerCommented:
If A1 and A2 contain the words "Rare" and "Insignificant" then you can use

=INDEX(D6:H10,MATCH(A1,\$C\$6:\$C\$10,0),MATCH(A2,\$D\$5:\$H\$5,0))
0

Commented:
use of index and match like this example
http://skp.mvps.org/xl00002.htm

will provide you with a sample file with your data shortly
0

Commented:
your should "lock" the data range in the formula with absolute reference in order to be able to copy the formula properly.
0

Author Commented:
Thank you, and apologies for the delay in responding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.