Pulling a Rating from a Matrix

Berto D

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!

Dave Stanley

@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.

Dave Stanley

@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

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.

Berto D

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