Excel: Returning a value from a Risk matrix

If you've ever needed to lookup values from a risk matrix, you'll know it's error-prone and time-consuming.

While there are methods of using LOOKUP and VLOOKUP and INDEX and MATCH to return matrix values, these result in long formulas that are hard to understand.

This web page describes a risk matrix lookup formula that is very easy to understand and modify.

Using OFFSET and MATCH to lookup a value from a matrix

The following is an example of syntax that combines OFFSET and MATCH, and uses named ranges for clarity:

   =OFFSET(A2,MATCH(Lookup_Value,likelihood,0),MATCH(Lookup_Value,consequence,0))

For instance, if Q1 has the likelihood, and R1 has the consequence, then the following formula returns the corresponding level of risk.

=OFFSET(risk_matrix_origin,MATCH(Q1,likelihood,0),MATCH(R1,consequence,0))

Note that the following would need to be added as named ranges:

• risk_matrix_origin: Top-left cell (A1)

• likelihood: Labels of likelihood (A3:A8)

• consequence: Labels of consequence (B2:G2)

For more information, see: https://support.microsoft.com/en-us/office/use-excel-built-in-functions-to-find-data-in-a-table-or-a-range-of-cells-6777ec9b-6191-426a-8d45-196ecbf2a186

Below is an example showing a risk matrix which has numerical range values. By using an array formula and some string to value mangling, it's possible to mix and match both text and number matching.