• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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
0
greg_c
Asked:
greg_c
  • 3
  • 2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now