Risk Matrix Index and Match Problem

[cwren007]

Hello once again people.
I have a problem i have been trying and it has been trying me.
I am try to index and Match from a risk 5x5 table and place the corresponding outcome from the risk matrix.
I have tried various formula for indexing and matching with a result of "Value"or Ïndex"in the cell i want the out come to be in.
So if cell "B3"=serious" and Cell "C"3 = "Likely" then in cell "D" = "High" and the cell is YELLOW with "HIGH" txt in the Cell.
The Pic below shows the excel spreadsheet and Risk matrix . This is how it is on my Spreadsheet.

A

B

C

D

T

U

V

W

X

Y

1

Consequence

Rare

Likelihood

Possible

Likely

Certanty

2

Consequence

LikelyHood

Risk

5

Moderate

high

Extreme

Extreme

Extreme

3

QA System

Serious

Moderate

High

4

Moderate

High

High

Extreme

Extreme

4

Qp1

Major

Likely

Extreme

3

Moderate

Moderate

High

High

Extreme

5

QP2

Minor

possible

Low

2

Low

Low

Moderate

High

High

6

QP3

1

Low

low

Moderate

Moderate

High

7

QP4

8

QP5

<tbody>
</tbody>

Column "D" is calculated based on the combination of columns "b"and "C taken from Columns U to Y and Rows 2 to 6

If any one can help that would be much appreciated

thanks
Chris

 

[Giordano Bruno]

Chris. I cannot follow your layout. Surely you should have an array showing Consequence in Row 1 and Likelihood in column T. The output should be an index match of these two options. You show in B3 a consequence of "Serious". this is not listed in your Consequence options. The likelihood in C3 is "Moderate", but you have only numbers in the likelihood column. There is no way that you could use such an array in the way you suggest.

 

[cwren007]

Chris. I cannot follow your layout. Surely you should have an array showing Consequence in Row 1 and Likelihood in column T. The output should be an index match of these two options. You show in B3 a consequence of "Serious". this is not listed in your Consequence options. The likelihood in C3 is "Moderate", but you have only numbers in the likelihood column. There is no way that you could use such an array in the way you suggest.

Thanks for the reply Giordano,
Actually got the formula to work. the issue was my selection criteria was slightly different in the formula to the selection list. so a space in one selection where there should have not been a space.
Anyway thanks for your assistance
and formula now works perfectly
Regards
Chris