Excel Risk Matrix - Automated Score Calculation

Hello All,

Firstly, thank you to anyone taking time to read this

I am trying to put together a risk matrix, that automatically calculates a score depending on the conditions of two cells. I have attached a non-specific template as an for reference.

My initial thought was to make use of the IF statement, however I anticipate I will have to write various statements due to the potential variations in options.

What I require, is F14 to return the Risk rating that would be calculated from the risk matrix.

I've tried to explain this the best I can, but if you have any questions please fire away I will be active.

Many thanks,
Michael

Try

=INDEX($E$6:$I$10,MATCH($D$14,$B$6:$B$10,0),MATCH($E$14,$E$2:$I$2,0))

=iferror(index($e$6:$i$10,match(d14,$b$6:$b$10,),match(e14,$e$2:$i$2,)),"")

Well, you made that look incredibly easy!! Thank you so much.

Before I mark as solved, I'm going to build it into the actual spread sheet.

For my development, could you breakdown the formula with some notes on what it is actually doing? (Not 100% necessary)

=INDEX($E$6:$I$10,MATCH($D$14,$B$6:$B$10,0),MATCH($E$14,$E$2:$I$2,0))

INDEX formula is INDEX(Range, row, column) so we find the intersection of row & column in the range

The first MATCH finds the row number by comparing D14 against headings in B6:B10 : so if the MATCH is B6 we get a result of 1 (first row)

And the same for the column where we match E14 against the Column headings in E2:I2

So we now have a row and column number which enables INDEX to return the value from the range E6:I10

if D14="Catastrophic" then we will get a row value of 5 and if E14 is "Certain" we get a column value of 5

So using your ratings row/column as a "guide" you will see we get a value of 25 in F14

Click on cell F14, then Select "FORMULAS" ==>"Evaluate Formula"==>"Evaluate" and you will see how the formula works.

Hope this helps.