Where “impact” is the named range J6, and “certainty” is the named range J5
Context
A risk matrix is used for risk assessment. One axis is used to assign the probability of a particular risk and the other axis is used to assign consequence or severity. A risk matrix can a useful to rank the potential impact of a particular event, decision, or risk. In the example shown, the values inside the matrix are the result of multiplying certainty by impact, on a 5-point scale. This is a purely arbitrary calculation to give each cell in the table a unique value. The range C5:C9 defines the matrix values. What’s left is to figure out the correct row and column numbers, and for that we use the MATCH function. To get a row number for INDEX (the impact), we use: To get a column number for INDEX (the impact), we use: In both cases, MATCH is set up to perform an exact match. When certainty is “possible” and impact is “major” MATCH calculates row and column numbers as follows: The INDEX function then returns the value at the fourth row and third column, 12. For a more detailed explanation of how to use INDEX and MATCH, see this article.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
