Multiple IF statements in one cell

[Zenroxy]

Hoping to get some help with this:

I have 36 possibilities for one cell. Just wondering if it is possible to include them all? Thanks for your help.

=IF((AND(D2="L6 - Almost Unprecedented",E2="C6 - Insignificant")),"D","")
=IF((AND(D2="L6 - Almost Unprecedented",E2="C5 - Minor")),"D","")
=IF((AND(D2="L6 - Almost Unprecedented",E2="C4 - Moderate")),"D","")
=IF((AND(D2="L6 - Almost Unprecedented",E2="C3 - Major")),"D","")
=IF((AND(D2="L6 - Almost Unprecedented",E2="C2 - Severe")),"C","")
=IF((AND(D2="L6 - Almost Unprecedented",E2="C1 - Catastrophic")),"C","")
=IF((AND(D2="L5 - Very Unlikely",E2="C6 - Insignificant")),"D","")
=IF((AND(D2="L5 - Very Unlikely",E2="C5 - Minor")),"D","")
=IF((AND(D2="L5 - Very Unlikely",E2="C4 - Moderate")),"D","")
=IF((AND(D2="L5 - Very Unlikely",E2="C3 - Major")),"C","")
=IF((AND(D2="L5 - Very Unlikely",E2="C2 - Severe")),"C","")
=IF((AND(D2="L5 - Very Unlikely",E2="C1 - Catastrophic")),"B","")
=IF((AND(D2="L4 - Unlikely",E2="C6 - Insignificant")),"D","")
=IF((AND(D2="L4 - Unlikely",E2="C5 - Minor")),"D","")
=IF((AND(D2="L4 - Unlikely",E2="C4 - Moderate")),"C","")
=IF((AND(D2="L4 - Unlikely",E2="C3 - Major")),"C","")
=IF((AND(D2="L4 - Unlikely",E2="C2 - Severe")),"B","")
=IF((AND(D2="L4 - Unlikely",E2="C1 - Catastrophic")),"B","")
=IF((AND(D2="L3 - Likely",E2="C6 - Insignificant")),"D","")
=IF((AND(D2="L3 - Likely",E2="C5 - Minor")),"C","")
=IF((AND(D2="L3 - Likely",E2="C4 - Moderate")),"C","")
=IF((AND(D2="L3 - Likely",E2="C3 - Major")),"B","")
=IF((AND(D2="L3 - Likely",E2="C2 - Severe")),"B","")
=IF((AND(D2="L3 - Likely",E2="C1 - Catastrophic")),"A","")
=IF((AND(D2="L2 - Very Likely",E2="C6 - Insignificant")),"C","")
=IF((AND(D2="L2 - Very Likely",E2="C5 - Minor")),"C","")
=IF((AND(D2="L2 - Very Likely",E2="C4 - Moderate")),"B","")
=IF((AND(D2="L2 - Very Likely",E2="C3 - Major")),"B","")
=IF((AND(D2="L2 - Very Likely",E2="C2 - Severe")),"A","")
=IF((AND(D2="L2 - Very Likely",E2="C1 - Catastrophic")),"A","")
=IF((AND(D2="L1 - Almost Certain",E2="C6 - Insignificant")),"C","")
=IF((AND(D2="L1 - Almost Certain",E2="C5 - Minor")),"B","")
=IF((AND(D2="L1 - Almost Certain",E2="C4 - Moderate")),"B","")
=IF((AND(D2="L1 - Almost Certain",E2="C3 - Major")),"A","")
=IF((AND(D2="L1 - Almost Certain",E2="C2 - Severe")),"A","")
=IF((AND(D2="L1 - Almost Certain",E2="C1 - Catastrophic")),"A","")

 

[Alex Blakenburg]

You wouldn't do that using if statements.
My lookup table is called tblRiskMatrix. As long as you call it that you can put it anywhere in the workbook and the call it that the Table Formulas will work.

20210824 Risk Matrix.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Likelihood	Impact	Rating Tbl Version	Rating				Impact
2	L1	C4	A	A
3	L2	C3	B	B			Likelihood	C1	C2	C3	C4	C5	C6
4							L1	A	A	A	A	B	C
5							L2	A	A	B	B	C	C
6							L3
7							L4
8							L5
9							L6
10
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX(tblRiskMatrix,MATCH($D2,tblRiskMatrix[Likelihood],0),MATCH($E2,tblRiskMatrix[#Headers],0))
G2:G3	G2	=INDEX($J$3:$P$9,MATCH($D2,$J$3:$J$9,0),MATCH($E2,$J$3:$P$3,0))

 

[Toadstool]

Hi Zenroxy,

Alex has a more elegant solution. I'm just a kinda brute force guy.

Zenroxy.xlsx
	D	E	F	G	H	I	J	K	L
1	Probability	Impact	Result			D2	E2	Concatenated	Class
2	L5 - Very Unlikely	C6 - Insignificant	D			L6 - Almost Unprecedented	C6 - Insignificant	L6 - Almost UnprecedentedC6 - Insignificant	D
3	L5 - Very Unlikely	C1 - Catastrophic	B			L6 - Almost Unprecedented	C5 - Minor	L6 - Almost UnprecedentedC5 - Minor	D
4	L1 - Almost Certain	C6 - Insignificant	C			L6 - Almost Unprecedented	C4 - Moderate	L6 - Almost UnprecedentedC4 - Moderate	D
5	L6 - Almost Unprecedented	C7 - Disastrous	Unknown			L6 - Almost Unprecedented	C3 - Major	L6 - Almost UnprecedentedC3 - Major	D
6	L5 - Very Unlikely	C4 - Moderate	D			L6 - Almost Unprecedented	C2 - Severe	L6 - Almost UnprecedentedC2 - Severe	C
7	L5 - Very Unlikely	C3 - Major	C			L6 - Almost Unprecedented	C1 - Catastrophic	L6 - Almost UnprecedentedC1 - Catastrophic	C
8	L5 - Very Unlikely	C2 - Severe	C			L5 - Very Unlikely	C6 - Insignificant	L5 - Very UnlikelyC6 - Insignificant	D
9						L5 - Very Unlikely	C5 - Minor	L5 - Very UnlikelyC5 - Minor	D
10						L5 - Very Unlikely	C4 - Moderate	L5 - Very UnlikelyC4 - Moderate	D
11						L5 - Very Unlikely	C3 - Major	L5 - Very UnlikelyC3 - Major	C
12						L5 - Very Unlikely	C2 - Severe	L5 - Very UnlikelyC2 - Severe	C
13						L5 - Very Unlikely	C1 - Catastrophic	L5 - Very UnlikelyC1 - Catastrophic	B
14						L4 - Unlikely	C6 - Insignificant	L4 - UnlikelyC6 - Insignificant	D
15						L4 - Unlikely	C5 - Minor	L4 - UnlikelyC5 - Minor	D
16						L4 - Unlikely	C4 - Moderate	L4 - UnlikelyC4 - Moderate	C
17						L4 - Unlikely	C3 - Major	L4 - UnlikelyC3 - Major	C
18						L4 - Unlikely	C2 - Severe	L4 - UnlikelyC2 - Severe	B
19						L4 - Unlikely	C1 - Catastrophic	L4 - UnlikelyC1 - Catastrophic	B
20						L3 - Likely	C6 - Insignificant	L3 - LikelyC6 - Insignificant	D
21						L3 - Likely	C5 - Minor	L3 - LikelyC5 - Minor	C
22						L3 - Likely	C4 - Moderate	L3 - LikelyC4 - Moderate	C
23						L3 - Likely	C3 - Major	L3 - LikelyC3 - Major	B
24						L3 - Likely	C2 - Severe	L3 - LikelyC2 - Severe	B
25						L3 - Likely	C1 - Catastrophic	L3 - LikelyC1 - Catastrophic	A
26						L2 - Very Likely	C6 - Insignificant	L2 - Very LikelyC6 - Insignificant	C
27						L2 - Very Likely	C5 - Minor	L2 - Very LikelyC5 - Minor	C
28						L2 - Very Likely	C4 - Moderate	L2 - Very LikelyC4 - Moderate	B
29						L2 - Very Likely	C3 - Major	L2 - Very LikelyC3 - Major	B
30						L2 - Very Likely	C2 - Severe	L2 - Very LikelyC2 - Severe	A
31						L2 - Very Likely	C1 - Catastrophic	L2 - Very LikelyC1 - Catastrophic	A
32						L1 - Almost Certain	C6 - Insignificant	L1 - Almost CertainC6 - Insignificant	C
33						L1 - Almost Certain	C5 - Minor	L1 - Almost CertainC5 - Minor	B
34						L1 - Almost Certain	C4 - Moderate	L1 - Almost CertainC4 - Moderate	B
35						L1 - Almost Certain	C3 - Major	L1 - Almost CertainC3 - Major	A
36						L1 - Almost Certain	C2 - Severe	L1 - Almost CertainC2 - Severe	A
37						L1 - Almost Certain	C1 - Catastrophic	L1 - Almost CertainC1 - Catastrophic	A
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX($L$2:$L$37,MATCH(D2&E2,$K$2:$K$37,0)),"Unknown")
K2:K37	K2	=I2&J2

 

[Zenroxy]

You wouldn't do that using if statements.
My lookup table is called tblRiskMatrix. As long as you call it that you can put it anywhere in the workbook and the call it that the Table Formulas will work.

20210824 Risk Matrix.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Likelihood	Impact	Rating Tbl Version	Rating				Impact
2	L1	C4	A	A
3	L2	C3	B	B			Likelihood	C1	C2	C3	C4	C5	C6
4							L1	A	A	A	A	B	C
5							L2	A	A	B	B	C	C
6							L3
7							L4
8							L5
9							L6
10
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX(tblRiskMatrix,MATCH($D2,tblRiskMatrix[Likelihood],0),MATCH($E2,tblRiskMatrix[#Headers],0))
G2:G3	G2	=INDEX($J$3:$P$9,MATCH($D2,$J$3:$J$9,0),MATCH($E2,$J$3:$P$3,0))

Thanks Alex, much appreciated.

 

[Zenroxy]

Hi Zenroxy,

Alex has a more elegant solution. I'm just a kinda brute force guy.

Zenroxy.xlsx
	D	E	F	G	H	I	J	K	L
1	Probability	Impact	Result			D2	E2	Concatenated	Class
2	L5 - Very Unlikely	C6 - Insignificant	D			L6 - Almost Unprecedented	C6 - Insignificant	L6 - Almost UnprecedentedC6 - Insignificant	D
3	L5 - Very Unlikely	C1 - Catastrophic	B			L6 - Almost Unprecedented	C5 - Minor	L6 - Almost UnprecedentedC5 - Minor	D
4	L1 - Almost Certain	C6 - Insignificant	C			L6 - Almost Unprecedented	C4 - Moderate	L6 - Almost UnprecedentedC4 - Moderate	D
5	L6 - Almost Unprecedented	C7 - Disastrous	Unknown			L6 - Almost Unprecedented	C3 - Major	L6 - Almost UnprecedentedC3 - Major	D
6	L5 - Very Unlikely	C4 - Moderate	D			L6 - Almost Unprecedented	C2 - Severe	L6 - Almost UnprecedentedC2 - Severe	C
7	L5 - Very Unlikely	C3 - Major	C			L6 - Almost Unprecedented	C1 - Catastrophic	L6 - Almost UnprecedentedC1 - Catastrophic	C
8	L5 - Very Unlikely	C2 - Severe	C			L5 - Very Unlikely	C6 - Insignificant	L5 - Very UnlikelyC6 - Insignificant	D
9						L5 - Very Unlikely	C5 - Minor	L5 - Very UnlikelyC5 - Minor	D
10						L5 - Very Unlikely	C4 - Moderate	L5 - Very UnlikelyC4 - Moderate	D
11						L5 - Very Unlikely	C3 - Major	L5 - Very UnlikelyC3 - Major	C
12						L5 - Very Unlikely	C2 - Severe	L5 - Very UnlikelyC2 - Severe	C
13						L5 - Very Unlikely	C1 - Catastrophic	L5 - Very UnlikelyC1 - Catastrophic	B
14						L4 - Unlikely	C6 - Insignificant	L4 - UnlikelyC6 - Insignificant	D
15						L4 - Unlikely	C5 - Minor	L4 - UnlikelyC5 - Minor	D
16						L4 - Unlikely	C4 - Moderate	L4 - UnlikelyC4 - Moderate	C
17						L4 - Unlikely	C3 - Major	L4 - UnlikelyC3 - Major	C
18						L4 - Unlikely	C2 - Severe	L4 - UnlikelyC2 - Severe	B
19						L4 - Unlikely	C1 - Catastrophic	L4 - UnlikelyC1 - Catastrophic	B
20						L3 - Likely	C6 - Insignificant	L3 - LikelyC6 - Insignificant	D
21						L3 - Likely	C5 - Minor	L3 - LikelyC5 - Minor	C
22						L3 - Likely	C4 - Moderate	L3 - LikelyC4 - Moderate	C
23						L3 - Likely	C3 - Major	L3 - LikelyC3 - Major	B
24						L3 - Likely	C2 - Severe	L3 - LikelyC2 - Severe	B
25						L3 - Likely	C1 - Catastrophic	L3 - LikelyC1 - Catastrophic	A
26						L2 - Very Likely	C6 - Insignificant	L2 - Very LikelyC6 - Insignificant	C
27						L2 - Very Likely	C5 - Minor	L2 - Very LikelyC5 - Minor	C
28						L2 - Very Likely	C4 - Moderate	L2 - Very LikelyC4 - Moderate	B
29						L2 - Very Likely	C3 - Major	L2 - Very LikelyC3 - Major	B
30						L2 - Very Likely	C2 - Severe	L2 - Very LikelyC2 - Severe	A
31						L2 - Very Likely	C1 - Catastrophic	L2 - Very LikelyC1 - Catastrophic	A
32						L1 - Almost Certain	C6 - Insignificant	L1 - Almost CertainC6 - Insignificant	C
33						L1 - Almost Certain	C5 - Minor	L1 - Almost CertainC5 - Minor	B
34						L1 - Almost Certain	C4 - Moderate	L1 - Almost CertainC4 - Moderate	B
35						L1 - Almost Certain	C3 - Major	L1 - Almost CertainC3 - Major	A
36						L1 - Almost Certain	C2 - Severe	L1 - Almost CertainC2 - Severe	A
37						L1 - Almost Certain	C1 - Catastrophic	L1 - Almost CertainC1 - Catastrophic	A
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX($L$2:$L$37,MATCH(D2&E2,$K$2:$K$37,0)),"Unknown")
K2:K37	K2	=I2&J2

Thanks Toadstool, much appreciated