How to detect multiple dropdown values in a contains function?

Hello to All,

I am working on a sheet to verify that one column "Badge Selection" contains drop down values from another column with drop down values "Badges". I have a third column "Badge Verification" that returns a 'true' if values in "Badge Selection" contain values found in "Badge Verification". This formula works for 1 drop down value selected, but for some reason, when I have multiple drop down values selected in column "Badge Selection", column "Badge Verification" comes back as false.

Here is the formula used in column "Badge Verification".

=IF(CONTAINS([Badge Selection]@row, [Badge Verification]@row), "True", "False")

Any help is greatly appreciated, thank you!

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    You have your formula set to only read for one selection and not multiple. I don't think the CONTAINS function is what you want to work with for this example. You are probably going to need a VLOOKUP formula with other nested functions to produce the results you are looking for. I would need a bit more info to come up with a complete formula for this situation.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Hello,

    Here is an example of what I am trying to do:

    Column A "Fruits" has drop down values of "Apple", "Orange", and "Banana". This was put into the column properties.

    Column B has a column formula "=VLOOKUP([Fruits]@row, {Accepted Fruits Range 1}, 2, true)"

    "Accepted Fruits" sheet contains 2 columns, Column 1 has type person buying fruit "Customer 1", "Customer 2", etc. Column 2 shows what kind of fruit Customer is allowed to buy; e.g. Customer 1 will only be able to buy an "Apple" and/or a "Banana", whereas Customer 2 is only able to buy an "Orange" and/or "Banana".

    Currently if Customer 1 is filling out the form, a selection of Apple or Banana will populate for them, and they should be able to select 1 or both options.

    Column C is validation that Customer 1 selected values in Column A that are found in Column B. The formula is : =IF(CONTAINS([Column A]@row, [Column B]@row), "True", "False")

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @CIP Compliance Team

    First:

    Here is the formula used in column "Badge Verification".

    =IF(CONTAINS([Badge Selection]@row, [Badge Verification]@row), "True", "False")

    I am assuming you mean:

    =IF(CONTAINS([Badge Selection]@row, Badges@row), "True", "False")

    because a formula can't reference the cell it's in. Right?

    Next: There's no clean way to do what you want.

    In your example, you indicate that different customer types are filling out a form. You essentially want them to have a different list of choices from the list of Badges? If that's the case, then you'll need a bunch of different Badge Selection columns, and use conditional logic in the form that says If the customer field is Customer 1, show them Badge Selection 1, if customer field is Customer 2, show them badge selection 2, etc. Then you wouldn't need validation. In your sheet, use a main Badge Selection multi-select column with a formula that JOINS all the customer specific badge selection columns for each row, so that whichever selection column was filled in, the results all end up in the main column.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!