I am building out a risk register and I'd like to use a formula to calculate the Risk Level, it will have to pull multiple combinations to get the formula though and I'm hitting a snag in my IF(AND formula.

Essentially it needs to breakdown info input in my Risk Probability + Risk Impact columns to calculate the Risk Level (for all 25 combinations)

Rare + Insignificant = Low Risk

Rare + Minor = Low Risk

Rare + Moderate = Low Risk

Moderate + Major = Major Risk

etc, etc...


  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭


    This is great, hope more people will do Risk Scoring.

    Are you by chance using a number to represent each level? For example: Rare might be a 1 and Insignificant might be a 1 so that a total (1x1) would equal Low Risk, or 1 to 3 = Low since you might have a 1 and 3 with Rare/Mod, ec.. There are other ways but it makes some things easier using a value system assignment. It would cut down on your IF's. You could have a hidden col. just for the total, then a series of If's to assign a level. That should mean less IFs.

    If that doesn't work for your situation, please share the Formula and the matrix of results.

    Thank you,


    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 04/27/22

    You can Nest 25 IF statements, but I think using Index and Match is better. I made a video for you.

    We are going to create another sheet to store the scores. Then we will use INDEX and MATCH to connect the two. I use the "AIO" nomenclature to indicate All In One, but you can use whatever you like.

    Two sheets: Risk Probability + Risk Impact and Risk Probability + Risk Impact Reference

    Risk Probability + Risk Impact


    Risk Probability

    Risk Impact




    Index: =IFERROR(INDEX({Score}, (MATCH([Risk Probability]@row + [Risk Impact]@row, {AIO_}, 0))), "")

    =IFERROR(INDEX({Narrative}, (MATCH([Risk Probability]@row + [Risk Impact]@row, {AIO_}, 0))), "")

    Risk Probability + Risk Impact Reference


    Risk Probability

    Risk Impact





    AIO: =[Risk Probability]@row + [Risk Impact]@row

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!