Formulas for an automatically generated Risk Level
Hi
I'm am trying to replicate the below formulas in smartsheet so it can automatically calculate the Risk Level with existing controls and with additional mitigation. The below formulas are currently being used in excel:
Formula for existing controls Risk Level:
=IFERROR(VLOOKUP(H7&I7,LikeliConseq, 2, FALSE),"")
Formula for mitigation Risk Level:
=IF(AND(L7="",M7=""),J7,VLOOKUP(L7&M7,LikeliConseq, 2, FALSE))
I would like the end result to include the Risk Level in word format (e.g. Extreme, Medium, Low etc.) and the corresponding colour as per 'Risk Level Capture' attached.
Any advice?
Thanks
Comments

You would need to set your table up so that the Consequences are going across the top row above the table. Something like this (column names in bold):
.
TableA TableB TableC TableD TableE
Minor Moderate Major Severe
Certain
Likely
Possible
Unlikely
Rare
.
Of course you would fill in the rest of your table with the ratings.
From there you would use something along the lines of this:
=INDEX($TableB$2:$TableE$6, MATCH(Likelihood@row, $TableA$2:$TableA$6, 0), MATCH(Consequence@row, $TableB$1:$TableE$1, 0))
.
This leverages the MATCH function to give you a row number based on the likelihood and a column number based on the consequence to populate the appropriate fields in the INDEX function.
EDIT:
I forgot the color part...
You would simply use conditional formatting to get the colors to pull through.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thanks very much Paul!

Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!