Multiple criteria for If (contains) from list

CaseyMI

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.

DKazatsky2

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

DKazatsky2

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

CaseyMI

Hi @DKazatsky2 ,

Thank you for your help as that did solve the formula issue. Just wondering why the formula does no need the "false" like the original.

Once again thank you!

DKazatsky2

@CaseyMI,

Leaving the "false" blank or setting it to "", as you had done, are basically the same thing. You only need a value in the "false" if you want to show something.