How to Combine Multiple Formula for Matrix Table

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.

Answers

  • Kamil Ismail
    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
    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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Kamil Ismail
    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".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!