How to Combine Multiple Formula for Matrix Table

Kamil Ismail

Hi All,

As we know, not all formula can be used in Smartsheet. Need help on combining "IF" "OR" "INDEX" & "MATCH formula.

Currently we have a risk rating matrix where it depends on user selection on column "Consequences" & "Likelihood". Formula I apply in excel are: =IF(OR(G22="",F22=""),"",INDEX($J$12:$P$16,MATCH(G22,$I$12:$I$16,),MATCH(F22,$J$11:$P$11,)))

As for now, I have created the risk rating matrix on another sheet and how to implement this on Smartsheet? I have try many method and I stuck here.

I really appreciate someone could assist me on this.

Kamil Ismail

I have tried this formula and doesn't show anything.

=IF(OR([Q1]@row = " ", [Q2]@row = " "), INDEX([Primary Column]1:[Column6]6, MATCH([Q2]@row, [Primary Column]1:[Primary Column]6), MATCH([Q1]@row, [Primary Column]@row:[Column6]@row)))

Ramzi K

@Kamil Ismail

Can you please show the example of the Smartsheet sheet and not the Excel sheet. It would be easier to see what your formula correlates to. But at first look, you are checking for the value " " in your formula IF(OR([Q1]@row = " ", [Q2]@row = " ") ... " " will not work. If you are checking for a blank value, you need to remove the space. So it will look like this: IF(OR([Q1]@row = "", [Q2]@row = "") ...

I hope this is helpful. If not, please feel free to attach screen shots of you Smartsheet sheet.

Cheers,

Ramzi

Kamil Ismail

User will go to worksheet and submit data, row by row. Only two columns user need to select on dropdown list ("Consequences" & "Likelihood"), I have listed down five selection on each dropdown list option. Result will automatically shown on "Risk Rating" column. This result is based on matrix on another worksheet.

Primary column, is based on "Likelihood" dropdown option and the column 2, 3, 4, 5, 6 is the dropdown list option for "Consequences".