Risk Assessment formula conversion from excel to smartsheet

I'm trying to convert an excel formula to use in smartsheet without much success.

The excel formula is:

=IFERROR(MATCH($F4,'Lookup Table'!$B$3:$B$7,0)*MATCH($G4,'Lookup Table'!$B$3:$B$7,0),"")

In short, I need the 'Risk Matrix' column in smartsheet to show the score where the probability and impact rating intersect.

I didn't create the excel file (or formula) and am not a formula expert as is probably obvious from my question.

Can anyone help?

Capture 1.JPG

Find more posts tagged with

Formulas

Comments

L_123

The easy way or the hard way?

the easy way is to switch from using text and use numbers. Instead of very low - very high it would be 1 - 5 and all you have to do is multiply them.

=[Column2]1 * [Column3]1

the hard way is to convert the text into numbers then multiply them

=if([Column2]1 = "Very High",5,if([Column2]1 = "High", 4,if([Column2]1 = "Medium", 3, if([Column2]1 = "Low",2,1)))) * if([Column3]1 = "Very High",5,if([Column3]1 = "High", 4,if([Column3]1 = "Medium", 3, if([Column3]1 = "Low",2,1

Neither of these requires your risk matrix. Just change the column names to what yours are, and change the text if you are using just the letter instead of the full text.

L_123

If you really want to use the risk matrix for some reason, you need to use index match match.

=INDEX([Column4]1:[Column9]5, MATCH([Column2]1, [Column4]:[Column4], 0), MATCH([Column3]1, [Column4]5:[Column9]5, 0))

Faz Mussa

I thought about doing it the easy way round but wanted to maintain the status quo. The second solution worked perfectly. Thanks so much for your help.

kellyj

*Test Comment*

Tribeiro

Man, that is awesome! is there somehow you can share this smartsheet as a template?

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…