Pulling a Rating from a Matrix

Options

I have this data in a Smartsheet file (exported to Excel below) and I need to pull a Risk Rating from any of the nine combinations of two variables.

Thanks in advance for the help!


Tags:

Best Answer

  • Dave Stanley
    Dave Stanley Employee
    Answer ✓
    Options

    @Berto D I would use an INDEX(COLLECT()) formula for this. This can be done either on one sheet as my example below shows or as using cross sheet references for the ranges.

    Same sheet: =IFERROR(INDEX(COLLECT([Risk Rating]:[Risk Rating], Probability:Probability, P@row, Impact:Impact, I@row), 1), "")

    Cross Sheet (cross sheet references need to be set up first): =IFERROR(INDEX(COLLECT({Risk Rating}, {Probability}, P@row, {Impact}, I@row), 1), "")

    The formula works by looking at the values in the P (Probability) and I (Impact) columns and using these to query the matrix to return the Result (Risk Rating). The since there should only be one match the INDEX() function returns just the first value in the array. In the event there are no matches the IFERROR() function is used to return a blank. I've put a few examples of the returned results in the yellow section.


Answers

  • Dave Stanley
    Dave Stanley Employee
    Answer ✓
    Options

    @Berto D I would use an INDEX(COLLECT()) formula for this. This can be done either on one sheet as my example below shows or as using cross sheet references for the ranges.

    Same sheet: =IFERROR(INDEX(COLLECT([Risk Rating]:[Risk Rating], Probability:Probability, P@row, Impact:Impact, I@row), 1), "")

    Cross Sheet (cross sheet references need to be set up first): =IFERROR(INDEX(COLLECT({Risk Rating}, {Probability}, P@row, {Impact}, I@row), 1), "")

    The formula works by looking at the values in the P (Probability) and I (Impact) columns and using these to query the matrix to return the Result (Risk Rating). The since there should only be one match the INDEX() function returns just the first value in the array. In the event there are no matches the IFERROR() function is used to return a blank. I've put a few examples of the returned results in the yellow section.


  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    hi @Berto D

    You can create the following nested if statement with an AND formula. There are other ways using a second look up sheet and index match if you need other options,

    =if(and(probability@row="low", Impact@row="low"), "Low", if(and(probability@row="medium", impact@row="low", "Low" do this for all of your scenarios.

    have a good night.

    best,

    Brad

    www.MVPOPS.com

  • Berto D
    Berto D ✭✭
    Options

    Ho-Ya! It works. I ran out of brain cells to figure this out myself. Thanks for helping me out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!