Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Multiple criteria for If (contains) from list

I am creating a training matrix that will place an "x" in my cells if specific criteria (User Role) is met. The criteria differ per topic so I have created a reference form that I used in my formula.

=IF(OR($[User Role]11 = {Role to training matrix Range 1}, $[User Role]11 = {Role to training matrix Range 2}, $[User Role]@row = {Role to training matrix Range 3}, $[User Role]@row = {Role to training matrix Range 4}, $[User Role]@row = {Role to training matrix Range 5}), "X", "")

Is there any way to select the range that has my criteria instead of each one being individually referenced? This ability will help ensure if the criteria changes then I do not need to manually change every formula.



Best Answer

  • Community Champion
    Answer ✓

    Hi @CaseyMI,

    Try using this formula,

    =IF(CONTAINS([User Role]@row, {Role to training matrix Range}), "X")

    {Role to training matrix Range} is the entire column "Stock Raw Material Receiving" from the second sheet.

    Hope this helps,

    Dave

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions