Risk Matrix

Hi

I am trying to make a sheet that automatically gives the answer of the risk level depending on input.

The input to the matrix is two cells with a dropdown menu with 5 options. The values in the two cells are:

Frequency
Very likely
Likely
Rarely
Unlikely
Very Unlikely

Consequence
Insignificant
Considerable
Serious
Severe
Disasterous

Depending on the values in the two cells the result should be looked up in matrix that looks like this:

	Insignificant	Considerable	Serious	Severe	Disasterous
Very likely	Unwanted	Unacceptable	Unacceptable	Unacceptable	Unacceptable
Likely	Acceptable	Unwanted	Unacceptable	Unacceptable	Unacceptable
Rarely	Acceptable	Unwanted	Unwanted	Unacceptable	Unacceptable
Unlikely	Negligble	Acceptable	Unwanted	Unwanted	Unacceptable
Very unlikely	Negligble	Acceptable	Acceptable	Unwanted	Unwanted

So for instance if the frequency cell says "Likely" and the consequence cell says "considerable" the third cell should automatically say "Unwanted". I tried with a very long IF statement but there is a limit on how much i can nest so that solution doesn't work.

Thank you in advance for your help

You need INDEX and match

If your grid is in A1:F6, your likelihood is in cell A10, and the consequence is in B10 you'd need the formula:

=INDEX($B$2:$F$6,MATCH(A10,$A$2:$A$6,0),MATCH(B10,$B$1:$F$1,0))

Hey

It works:D:D:D:D. I spent almost 2 days trying to solve this, and you solved it in 7 minutes. You are amazing. Thanks alot:D

Oops double posted

Originally Posted by Stoffer

I spent almost 2 days trying to solve this, and you solved it in 7 minutes. You are amazing.

Not amazing - I've just already spent days trying to solve this

Glad it works.