If thing in column x is something and thing in column y is something else....errr
I'm trying to build a bit of logic for a risk assessment. I'm brand new to SS, so please excuse the n00b question.
Previously, my risk matrix was 5 x 5 Likelihood x Impact, where 4x3=12 and 3x4=12. 12 was the risk rating.
Now, "Possible 3" x "High 4" = "12 High".......but "Likely 4" x "Moderate 3" = 12 Medium which has shot my very simple formula out the window.
Is there a way to, based on the word value in columns x and y, automatically have resulting value (Medium Risk or High Risk for example) inserted into the third column z?
Best Answer

So my suggestion would be to create the chart in a separate sheet.
Then you would use that same formula above but use cross sheet references created to match what is described in the formula.
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!
Answers

If you change your reference table labels (first column and row 6) to match exactly what the in sheet selections are, you can use something like this...
=INDEX([1  Low]1:[5  Extreme]5, MATCH([Likelihood (x)]@row, Likelihood1:Likelihood5, 0), MATCH([Impact (y)]@row, [1  Low]6:[5  Extreme]6, 0))
Basically you are using the INDEX function to pull the appropriate value. The first MATCH function gives us a row number based on the likelihood match in the first column of your table, and the second MATCH function tells us which column to pull from based on the match of Impact there across the bottom row.
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 for the response!
Sorry  I should have been more clear  the coloured table is out of a separate document  so not a reference point. It is the "policy", and I'm building (attempting to anyway) the tool.
How I intend it to work, is my users will select 1 of 5 options from each of the dropdowns in columns "Likelihood" and "Impact"  displayed below (previously referred to as columns X and Y):
I'd then like the column "Inherent Risk Rating" (previously referred to as column Z) to be autopopulated with the calculated product of the 2 columns, based on the risk matrix below:
Essentially the calculation is Likelihood x Impact = Inherent Risk Rating...but my problem is that "Possible x High = High" but "Likely x Moderate = Medium"  so assigning numeric values is no longer of use because the Risk Rating of "3 x 4" does not equal the same Risk Rating of "4 x 3".
Again, thanks very much for your assistance!

So my suggestion would be to create the chart in a separate sheet.
Then you would use that same formula above but use cross sheet references created to match what is described in the formula.
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
 62.9K Get Help
 378 Global Discussions
 208 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!