Microsoft Excel – using INDEX and MATCH with a Risk Matrix

ifonlyidknownthat

A risk matrix may look something like

Pic1
with likelihood of risk occurring being related to the severity of the risk.

If we have two columns of data, one representing the likelihood of the risk and the other the severity of the risk, then a third column should be able to use a formula to give the relevant rating.

Pic2

I have named cells b2 to E5 in the first screenshot as Risks and they are in a worksheet called Risk Rating.

The relevant formula to go in cell D2 is

=(INDEX(Risks,MATCH(C2,’Risk Rating’!$A$2:$A$5,0),MATCH(B2,’Risk Rating’!$B$1:$E$1,0))

Where the consequence in C2 is matched in the risk rating cells A2 to A5 and the likelihood in B2 is matched in the risk rating cells B1 and E1 and the intersection of both is returned.

I added the IFERROR function to allow copying of the formula down column D before values have been input in…

View original post 25 more words

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s