Formula Help - Risk Matrix

[cwim1990]

Can someone help me fix this formula for the section in RED.
As it stands I am just a '0' result, I have tried other ways such as IF(AND(L2="Almost Certain", M2="Extreme"), "Extreme", "Extreme") but then I get an error with any result as extreme as the consequence. I am assuming its an easy fix for someone more capable than I am with excel
For reference - this is the matrix requirements:

Rare	Insignificant	LOW
Rare	Minor	LOW
Rare	Moderate	LOW
Rare	Major	MEDIUM
Rare	Extreme	HIGH
Unlikely	Insignificant	LOW
Unlikely	Minor	LOW
Unlikely	Moderate	LOW
Unlikely	Major	MEDIUM
Unlikely	Extreme	HIGH
Possible	Insignificant	LOW
Possible	Minor	MEDIUM
Possible	Moderate	MEDIUM
Possible	Major	HIGH
Possible	Extreme	HIGH
Likely	Insignificant	LOW
Likely	Minor	MEDIUM
Likely	Moderate	HIGH
Likely	Major	HIGH
Likely	Extreme	EXTREME
Almost Certain	Insignificant	MEDIUM
Almost Certain	Minor	HIGH
Almost Certain	Moderate	HIGH
Almost Certain	Major	EXTREME
Almost Certain	Extreme	EXTREME

=IF(OR(L2="", M2=""), "NIL",
IF(AND(L2="Rare", M2="Insignificant"), "Low",
IF(AND(L2="Rare", M2="Minor"), "Low",
IF(AND(L2="Rare", M2="Moderate"), "Low",
IF(AND(L2="Rare", M2="Major"), "Medium",
IF(AND(L2="Rare", M2="Extreme"), "High",
IF(AND(L2="Unlikely", M2="Insignificant"), "Low",
IF(AND(L2="Unlikely", M2="Minor"), "Low",
IF(AND(L2="Unlikely", M2="Moderate"), "Low",
IF(AND(L2="Unlikely", M2="Major"), "Medium",
IF(AND(L2="Unlikely", M2="Extreme"), "High",
IF(AND(L2="Possible", M2="Insignificant"), "Low",
IF(AND(L2="Possible", M2="Minor"), "Medium",
IF(AND(L2="Possible", M2="Moderate"), "Medium",
IF(AND(L2="Possible", M2="Major"), "High",
IF(AND(L2="Possible", M2="Extreme"), "High",
IF(AND(L2="Likely", M2="Insignificant"), "Low",
IF(AND(L2="Likely", M2="Minor"), "Medium",
IF(AND(L2="Likely", M2="Moderate"), "High",
IF(AND(L2="Likely", M2="Major"), "High",
IF(AND(L2="Likely", M2="Extreme"), "Extreme",
IF(AND(L2="Almost Certain", M2="Insignificant"), "Medium",
IF(AND(L2="Almost Certain", M2="Minor"), "High",
IF(AND(L2="Almost Certain", M2="Moderate"), "High",
IF(AND(L2="Almost Certain", M2="Major"), "Extreme",
IF(AND(L2="Almost Certain", M2="Extreme"), "Extreme",)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

 

[bebo021999]

Why not using lookup table like this?
In N2: shorter formula, using lookup table
In N5: longer vesion, without lookup table (by harcording lookup table value)

CALCULETOR - Copy.xlsx
	L	M	N	O	P	Q	R	S	T
1							LOOKUP TABLE
2	Rare	Insignificant	LOW				Rare	Insignificant	LOW
3							Rare	Minor	LOW
4							Rare	Moderate	LOW
5	Unlikely	Extreme	HIGH				Rare	Major	MEDIUM
6							Rare	Extreme	HIGH
7							Unlikely	Insignificant	LOW
8							Unlikely	Minor	LOW
9							Unlikely	Moderate	LOW
10							Unlikely	Major	MEDIUM
11							Unlikely	Extreme	HIGH
12							Possible	Insignificant	LOW
13							Possible	Minor	MEDIUM
14							Possible	Moderate	MEDIUM
15							Possible	Major	HIGH
16							Possible	Extreme	HIGH
17							Likely	Insignificant	LOW
18							Likely	Minor	MEDIUM
19							Likely	Moderate	HIGH
20							Likely	Major	HIGH
21							Likely	Extreme	EXTREME
22							Almost Certain	Insignificant	MEDIUM
23							Almost Certain	Minor	HIGH
24							Almost Certain	Moderate	HIGH
25							Almost Certain	Major	EXTREME
26							Almost Certain	Extreme	EXTREME
Sheet2

Cell Formulas
Range	Formula
L2:M2	L2	=R2
N2	N2	=LOOKUP(2,1/($R$2:$R$26=L2)/($S$2:$S$26=M2),$T$2:$T$26)
L5	L5	=R9
M5	M5	=S11
N5	N5	=LOOKUP(2,1/({"Rare";"Rare";"Rare";"Rare";"Rare";"Unlikely";"Unlikely";"Unlikely";"Unlikely";"Unlikely";"Possible";"Possible";"Possible";"Possible";"Possible";"Likely";"Likely";"Likely";"Likely";"Likely";"Almost Certain";"Almost Certain";"Almost Certain";"Almost Certain";"Almost Certain"}=L5)/({"Insignificant";"Minor";"Moderate";"Major";"Extreme";"Insignificant";"Minor";"Moderate";"Major";"Extreme";"Insignificant";"Minor";"Moderate";"Major";"Extreme";"Insignificant";"Minor";"Moderate";"Major";"Extreme";"Insignificant";"Minor";"Moderate";"Major";"Extreme"}=M5),{"LOW";"LOW";"LOW";"MEDIUM";"HIGH";"LOW";"LOW";"LOW";"MEDIUM";"HIGH";"LOW";"MEDIUM";"MEDIUM";"HIGH";"HIGH";"LOW";"MEDIUM";"HIGH";"HIGH";"EXTREME";"MEDIUM";"HIGH";"HIGH";"EXTREME";"EXTREME"})

 

[cwim1990]

Thankyou for the quick response.
In the main sheet1 I have a risk register, with drop down options to choose the likelihood/consequence. I am not really using the table at all - it was more just to reference what the matrix was.
I will have a play around though using the hardcoding. thankyou!