Risk Matrix INDEX & MATCH issue in Excel

[AWE_David]

Hi All,

I'm having an issue with an excel INDEX and MATCH formula for the risk matrix below.
The formula I've used is:
=IFERROR(INDEX('OHS Risk Matrix'!$C$4:$G$8,MATCH(C24,'OHS Risk Matrix'!$C$3:$G$3,0),MATCH(B24,'OHS Risk Matrix'!$B$4:$B$8,0)),"")

It looks like it worked perfectly, however on some of the ratings, I'm getting a ranking that is different to the table. For example, Almost certain and minor it is giving me a critical ranking even though it is high in the table. Does anyone know why this is happening?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Consequence[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Catastrophic[/TD]
[TD="align: center"]Major[/TD]
[TD="align: center"]Moderate[/TD]
[TD="align: center"]Minor[/TD]
[TD="align: center"]Negligible[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Almost Certain[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]High[/TD]
[TD="align: center"]Moderate[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Likely[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]High[/TD]
[TD="align: center"]Moderate[/TD]
[TD="align: center"]Low[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Probability[/TD]
[TD="align: center"]Possible[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]High[/TD]
[TD="align: center"]Moderate[/TD]
[TD="align: center"]Low[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Unlikely[/TD]
[TD="align: center"]Critical[/TD]
[TD="align: center"]High[/TD]
[TD="align: center"]Moderate[/TD]
[TD="align: center"]Low[/TD]
[TD="align: center"]Low[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rare[/TD]
[TD="align: center"]High[/TD]
[TD="align: center"]Moderate[/TD]
[TD="align: center"]Low[/TD]
[TD="align: center"]Low[/TD]
[TD]Low[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Probability[/TD]
[TD]consequence[/TD]
[TD]risk rating[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Almost Certain[/TD]
[TD]Catastrophic[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Almost Certain[/TD]
[TD]Major[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Almost Certain[/TD]
[TD]Moderate[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Almost Certain[/TD]
[TD]Minor[/TD]
[TD]Critical (should be high)[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Almost Certain[/TD]
[TD]Negligible[/TD]
[TD]High (should be moderate)[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully this makes sense.

Regards,
Dave

 

[mrshl9898]

You're back the front

=IFERROR(INDEX('OHS Risk Matrix'!$C$4:$G$8,MATCH(B24,'OHS Risk Matrix'!$B$4:$B$8,0),MATCH(C24,'OHS Risk Matrix'!$C$3:$G$3,0)),"")

 

[AWE_David]

This is one of those "i've been looking at too many excel formula" moments.