Microsoft Excel – using INDEX and MATCH with a Risk Matrix

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 columns B and C without showing an error message.

Learn more about our Microsoft Excel training London, Middlesex, Surrey or our Microsoft Excel training Dorset, Wiltshire, Hampshire UK, or our Microsoft Excel training Sydney, Australia.

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 Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013 and tagged , , . Bookmark the permalink.

2 Responses to Microsoft Excel – using INDEX and MATCH with a Risk Matrix

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