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

Mike.A
Mike.A ✭✭
edited 12/31/20 in Formulas and Functions

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

image.png

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)

or

=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!

Tags:

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!