Convert High, Medium, Low to numbers (without nested IF statements)?

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

For each row in a sheet, I need to convert High/Medium/Low IMPACT and EFFORT text into numeric scores like the table below.

I know I can do this with nested IF statements, but was able to do this much more simply in google sheets using VLOOKUP or CHOOSE functions:

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

I cannot find a CHOOSE function in smartsheets, and the VLookup does not seem to let me replace the "range" reference with and array (like {"Low",1;"Medium",2;"High",2}).

Does anyone know if this can be done in smartsheets, or am I stuck with the following more complicated nested IF? Advice appreciated!

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


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Mike.A ,

    That's a beautiful nested IF statement and I think it's your best solution in Smartsheets. You could simplify the formula by using helper columns to do some of the calculations outside the cell formula. I don't think that gains you any real advantage though.

    Lots of really smart people in the Community. Maybe someone has a creative idea for you.

    Happy New Year.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Ben Goldblatt

    Hi @Mike.A,

    I agree that your nested IF formula may be the way to go here and I highly recommend Submitting a Product Enhancement Request to see about including a CHOOSE function in a future release.

    You may be able to get the formula results you're looking for using a combination of the INDEX, COLLECT, and JOIN functions but this would require some "helper" columns (as Mark pointed out) to list out the number values and to join together the IMPACT and EFFORT values. Here's an example of what this would look like, going off of the logic used in your IF statement:

    So the number values are listed out in the "Help Table" section of my sheet's Primary Column and I'm using a column-level formula to join the IMPACT and EFFORT values together in my "JOIN" column.

    • =JOIN([OBJ: IMPACT]@row:[OBJ: EFFORT]@row, " ")

    In the "Data Rows" section of the sheet, if I change either the IMPACT or the EFFORT value, the JOIN value on the row will update and the number will populate accordingly. I'm using the following formulas in rows 12 through 20 in my "Formula results" column:

    • =INDEX(COLLECT([Primary Column]2:[Primary Column]10, JOIN2:JOIN10, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]3:[Primary Column]11, JOIN3:JOIN11, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]4:[Primary Column]12, JOIN4:JOIN12, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]5:[Primary Column]13, JOIN5:JOIN13, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]6:[Primary Column]14, JOIN6:JOIN14, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]7:[Primary Column]15, JOIN7:JOIN15, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]8:[Primary Column]16, JOIN8:JOIN16, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]9:[Primary Column]17, JOIN9:JOIN17, JOIN@row), 1)
    • =INDEX(COLLECT([Primary Column]10:[Primary Column]18, JOIN10:JOIN18, JOIN@row), 1)

    Feel free to give this a try to see if this will work with what you have set up.

    I hope this helps!



  • Mike.A
    Mike.A ✭✭

    Thanks @Ben G and @Mark Cronk. Interesting and creative ideas. Nice to have the community help. I was trying to avoid helper columns if possible as it just adds more dependencies just to do the calculation. For now will stick with the nested IFs.

    The thing google does better here (that allows a lot of simplification) is not just having functions like CHOOSE, but more importantly, allowing a list of values to be used instead of a range in the VLookup (and other functions).


    I will find some time to submit an enhancement request and see what happens!

  • Mike.A
    Mike.A ✭✭

    Enhancement Request submitted: 2020-12-31.

    Smartsheet Product Enhancement Requests

    Category Formulas Enhancement Request

    Title: Allow use of value list (array) in place of Range arrays in functions like VLOOKUP

    Enhancement Request Description

    For each row in a sheet, I need to convert High/Medium/Low IMPACT and EFFORT text into numeric scores (see attached table).

    I know I can do this with extremely complicated nested IF statements or helper columns, but in Google sheets I am was able to do this much more simply using VLOOKUP or CHOOSE functions. The key difference is google allows you to replace the lookup Range reference with an array of values, making it very simple to translate text like "High/Med/Low" to numbers.


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

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

    SMARTSHEET NESTED IF (much more complicated and error prone).

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

    see post for more details:

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Excellent. Thank you for using the Community and bringing ideas about new capabilities.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mike.A
    Mike.A ✭✭

    Sorry there was a double posting on this. My first didn’t seem to show up so I ended up posting again! Oh well... :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!