Trying to Output a text value in a column based on values in two other columns.

kylorem

I am creating a risk register where Impact/Severity is chosen as a dropdown between 5 values (Severe, Major, Moderate, Minor, Not significant) and Probability is chosen between 5 values as well (Almost Certain, Likely, Possible, Unlikely, Rare) where depending on what was chosen in the two columns an output in a third column called Risk Score will result in one of the following values (Very High, High, Medium, Low).

Basically I want to create column that will output the value seen in this risk matrix:

What formula should I use? I tried using IFs and ANDs but I could only get one specific case output.

Thanks in advance!

Austin Smith

You want an index match match formula - google will help a ton here

=index(whole chart reference, match(likelihood refence cell, likelihood statuses column), match(impact reference cell, impact statuses row))

it selects a row based on the first criteria, a column on the second, and returns the meeting point