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!

Find more posts tagged with

Accepted answers

All comments

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.

Paul Newcome

Are you able to provide some screenshots with sample data for context?

CIP Compliance Team

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

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.