Risk Score Bucket Using VLOOKUP – Excel & Google Sheets

This tutorial will demonstrate how calculate a risk score bucket using VLOOKUP in Excel and Google Sheets.

risk score bucket vlookup 01

A risk score matrix is a matrix that is used during risk assessment in order to calculate a risk value by inputting the likelihood and consequence of an event.  We can create a simple risk score matrix in Excel by using the VLOOKUP and MATCH functions.

Risk Matrix Excel

First, we would need to lay out the risk matrix as shown below.

risk score bucket vlookup 02

Creating Drop Down Lists

Next, we can use Data Validation to create drop down lists for the Consequence and Likelihood cells.

  1. Click in E10.
  2. In the Ribbon, select Data>Data Validation.

risk score bucket vlookup 03

  1. Select List from the drop-down list provided.

risk score bucket vlookup 04

  1. Highlight D3:H3 and then click OK.

risk score bucket vlookup 05

You will now have a dropdown list displaying the different categories for the Consequences of the Risk assessment.

risk score bucket vlookup 06

Repeat the data validation for the Likelihood list.

risk score bucket vlookup 07

 

AutoMacro - VBA Code Generator

Calculate the Risk Score using VLOOKUP and MATCH

Once the Risk Matrix is setup, we can now use the VLOOKUP and MATCH functions to lookup the Risk Factor from the matrix.

MATCH

First, we use the MATCH function to find out which row we want the VLOOKUP function to look for.

risk score bucket vlookup 08

The MATCH Function above shows us that the Consequence ‘Moderate’ shown in E10 is in column 4 of the range C3:H3.  This is therefore the column that the VLOOKUP function will look in for the risk factor.

VLOOKUP

We now use the VLOOKUP function to look up the Likelihood factor which is show as ‘Possible’ in E11.

risk score bucket vlookup 09

In the above example, the VLOOKUP is using the value in E14 as the column to look up.    Joining the MATCH and VLOOKUP Functions will give us our original formula.

risk-score-bucket-vlookup-10

If you the Data Validation drop down lists to amend the Consequence and Likelihood criteria, the risk factor will change accordingly.

risk score-bucket vlookup 11

Risk Score Bucket Using VLOOKUP in Google Sheets

The Excel examples shown above work the same in Google Sheets as they do in Excel.

risk score bucket vlookup 12

Data Validation in Google Sheets

To create the dropdown list for the Consequence and Likelihood criteria, click in the cell where you want the list to go ex: E10.

From Menu bar, select Data >Data Validation.4

risk score bucket vlookup 13

Select List from a range in the Criteria drop down box, and then select the range where the list is stored ex: D3:H3.

risk score bucket vlookup 14

Click Save.

risk score bucket vlookup 15

Your drop down list will be created.

Repeat the process for the Likelihood dropdown box.

risk score bucket vlookup 16

Click Save.

 

risk score bucket vlookup 17

 

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!