I want to create an equation based on complex matrix

HexD

I am trying to change the status of a column, base on the top five impact levels ( Not Significant, Minor, Moderate, Major and severe) based on the conditions below. Would I have to nest in 25 equations, or does smartsheets have a simpler way of reaching this solution?

Paul Newcome

If you recreate this matrix in another sheet, you can use something along the lines of

=INDEX({Table Cells}, MATCH(Impact@row, {Impact Column}, 0), MATCH(Severity@row, {Top Row}, 0))

In the below snippet, green cells a re the first range, yellow cells are the second range, and red cells are the third range.

HexD

Just for clarification, I created two seperate columns, one for impact and the other for probability, so I want the third column to change status based on the combinations in this matrix

Paul Newcome

If you recreate this matrix in another sheet, you can use something along the lines of

=INDEX({Table Cells}, MATCH(Impact@row, {Impact Column}, 0), MATCH(Severity@row, {Top Row}, 0))

In the below snippet, green cells a re the first range, yellow cells are the second range, and red cells are the third range.

HexD

Thank you!

So I would definitely have to recreate this matrix in a separate smartsheet grid.

Paul Newcome

That would be the easiest way. Otherwise you are looking at a large nested IF formula.