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
greg_cAsked:
Who is Participating?
 
xtermieCommented:
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
 
Saqib Husain, SyedEngineerCommented:
=INDEX(D6:H10,MATCH("Rare",$C$6:$C$10,0),MATCH("Insignificant",$D$5:$H$5,0))
0
 
Saqib Husain, SyedEngineerCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
xtermieCommented:
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
 
xtermieCommented:
your should "lock" the data range in the formula with absolute reference in order to be able to copy the formula properly.
0
 
greg_cAuthor 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.

All Courses

From novice to tech pro — start learning today.