  Get instant live expert help with Excel or Google Sheets “My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Learn from a Risk Matrix Example in Excel

We can apply a risk matrix to a set of data to determine the risk that a hazard poses. We can do this with the INDEX and MATCH function. In this Matrix, the Y-axis tells us the SEVERITY of the risk involved and the X-axis tells us the LIKELIHOOD of occurrence of the RISK. Hence, the RISK of a hazard is calculated as:

`Risk of hazard = likelihood of occurrence (probability) * Severity of harm`

We will walk through the steps below to understand the process. Figure 1: How to Use a Risk Matrix

## Formula

The formula in Cell D13 is given as:

`=INDEX(C5:G9,MATCH(Severity,B5:B9,0),MATCH(Likelihood,C4:G4,0))`

## Setting up the Data

We will set up the risk matrix by doing the following:

• We will type the severity on the Y-axis (Cell B5 to Cell B9)
• We will type the likelihood on the X-axis (Cell C4 to Cell G4)
• We will input the numbers as shown in figure 2 in the range of C5:G9
• Cell C11, C12, and C13 will be named likelihood, severity, and result respectively
• Cell D11 and D12 will be named as RARE and MODERATE respectively Figure 2: Setting up the Risk Matrix Data

## Determining the Risk

• We will click on Cell E11
• We will type in this string: `=MATCH(D11,C4:G4,0)`
• Click on Enter
• Likewise, we will click on Cell E12
• We will type in this string: `=MATCH(D12,B5:B9,0)`
• We will click on Enter Figure 3: Determining the Result of the Inputted Data in the Risk Matrix

We will type the formula below into Cell D13

`=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))` Figure 4: Determining the Result of the Inputted Data in the Risk Matrix

We will press enter Figure 5: Result of the Inputted Data in the Risk Matrix

## Explanation

• MATCH function

The match function matches the lookup value within a specified range and we have set it to return an exact match. For instance, when determining the likelihood in the example, we typed in the string below:

`=MATCH(D11,C4:G4,0)`

D11 is the lookup value, C4:G4 is the range and zero indicates an exact match.

The same scenario applies to determine the severity.

• INDEX function

The INDEX function returns the number or value that is present in ROW 3 and COLUMN 2 based on this string: `=INDEX(C5:G9,E11,E12)`

This method is preferred to the ALTERNATIVE METHOD because we can vary the SEVERITY and LIKELIHOOD in Cells D11 and D12 respectively to AUTOMATICALLY get the result.

## Alternative method

• We will click on Cell D13
• We will type the string below into the Cell
=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))
• We will click on Enter Figure 6: Result of the Alternative method of applying the Risk Matrix

## Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

### Did this post not answer your question? Get a solution from connecting with the expert. Another blog reader asked this question today on Excelchat:
Solution examples How can I automatically change different cells and the information they display if we select different option from a drop down list?
Solved by V. U. in 59 mins index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins I need a formula to calculate the MAX of a range of 167 rows in column D, then find the MAX of the next 167 rows in column D and so on continuously through the entire sheet. Example MAX D2:D169, D170:D337, D338:D505 etc...
Solved by I. Q. in 60 mins Hello, I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error. =INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0)) No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question. How do I fix this error? Thanks
Solved by F. H. in 40 mins I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.
Solved by D. D. in 11 mins ## Subscribe to Excelchat.co Another blog reader asked this question today on Excelchat: