Simpler way to convert High/Medium/Low symbols to score - without using nested IFs?

I am trying to "score" rows using Impact vs Effort columns.

I know I can do this using a complicated nested IF statement. In google sheets, I can more easily use VLookup or Choose like this:

=VLOOKUP(M4,{"Low",1;"Medium",2;"High",2},2,FALSE) / VLOOKUP(N4,{"Low",1;"Medium",2;"High",2},2,FALSE)

=CHOOSE(MATCH(M4,{"Low","Medium","High"},0),1,2,3) / CHOOSE(MATCH(N4,{"Low","Medium","High"},0),1,2,3)

For smartsheet, does anyone know of a way to use something simpler than the nested IF below? Can smartsheet VLookup use a string array as the range like gsheets (eg: {"Low","Medium","High"})?

=IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "High"), 1, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "High"), .67, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "High"), .33, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Medium"), 1.5, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Medium"), 1, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Medium"), .5, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Low"), 3, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Low"), 2, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Low"), 1)))))))))

Any insights appreciated!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You could also build it out on the same sheet and then hide the columns so they are not visible.

Mike.A

Yep. That’s the option I will have to explore as well. Thanks again.

ps: allowing arrays for range inputs would add a lot of flexibility without more columns.

All comments

Paul Newcome

If you have the grid built out somewhere in a sheet, you can use an INDEX/MATCH/MATCH.

=INDEX({Score Portion}, MATCH([OBJ: IMPACT]@row, {Impact Labels}, 0), MATCH([OBJ: EFFORT]@row, {Effort Labels}, 0))

The MATCH function pulls a value for where within a range the specified data falls. So we use a MATCH function to pull the row number based on the Impact and another MATCH function to pull the column number based on the Effort.

Now that we have row and column numbers, we drop them into the appropriate portion of the INDEX function, and there you go.

Mike.A

Thanks for the idea @Paul Newcome.

I don't have that "grid" built out in smartsheet as it does not really fit my row structure. (wish smartsheet had "tabs" as well since I don't really want to create another loose sheet just for the grid). That is why I was checking if smartsheet allowed an array as a range input like google does ({"Low","Medium","High"}). This allows a huge amount of flexibility when trying to translate items without needing other physical columns. I guess not...

Will have consider the trade-off between error prone nested IF's and linking to an external sheet.

Thank for the quick response!

Paul Newcome

You could also build it out on the same sheet and then hide the columns so they are not visible.

Mike.A

Yep. That’s the option I will have to explore as well. Thanks again.

ps: allowing arrays for range inputs would add a lot of flexibility without more columns.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.