How to use index/match on multiselect dropdown list?

I am working on launching a global hiring form that uses this formula:

=IFERROR(IF(Country@row = INDEX({Approved Seats Country}, MATCH([Unique Seat Code]@row, {Approved Seats Unique Seat Code}, 0)), 0, 1), 2)

Basically this formula returns the number "1" by searching for the name of the country in the source sheet (Approved Seat Sheet) and matching the "Unique Seat Code" entered on the destination sheet with the "Unique Seat Code" on the source sheet (Approved Seats Sheet). The number "1" determines a workflow that is supposed to be followed.

However, the country column in the destination sheet and the country row in the source sheet are multi-select dropdown columns. So the problem is that when an individual wants to select one specific country and not the others, there will be an error because all of the countries have to be selected in order for the formula to work.

Is there a way to use index/match to make sure that the formula searches all of the options chosen on the source sheet and matches at least one of the options chosen from the source sheet?

Thank you! :)

Find more posts tagged with

Forms

Sheets

Formulas

Accepted answers

All comments

Leibel S

@DesireeJones

You would need to make a separate list/sheet of countries with 2 columns, one with country name (every country from your dropdowns gets its own row) the second column is a Multi-Select Dropdown with a formula that looks up your source sheets and brings in all the Seat codes that are associated with this country.

The formula would be something like the below (change the references as needed):

=JOIN(COLLECT({ref to seat code column in source sheet}, {ref to countries column in source sheet}, HAS(@cell, Country@row)), CHAR(10))

Once you have this list your destination sheet will look it up to determine if any of the countries selected in the form are associated with the selected seat code.

The formula would be something like the below (change the references as needed):

=IFERROR(IF(COUNT(COLLECT({ref to countries list country column}, {ref to countries list country column}, HAS([Countries]@row, @cell), {ref to countries list seat codes column}, HAS(@cell, [Unique Seat Code]@row))) > 0, 0, 1), 2)

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.