One of my criteria is one of many listed in a dropdown column and CONTAINS/HAS are eluding me.

Hello!

I am working from a single "Master Sheet" and utilizing INDEX/COLLECT to pull the desired info onto separate sheets. I am utilizing "Feature", "Level" and "Product Line" as my criteria, however the "Product Line" is a multi-select dropdown column with anywhere from 1-10 values in it depending on the products usage.

The multi-select seems to break it, but I cannot get the HAS or CONTAINS functions to work correctly. I will include an example below! The *** is where it breaks down on me.

=INDEX(COLLECT({Column Being Pulled}, {Level Selection}, [Level Selection]@row , {Feature Column}, Feature@row, {Product Line}, ******** ), 1)

Below is a screenshot of the column I am trying to grab from. An example of the value I want to identify is "Arbor - COA".

Find more posts tagged with

collect

INDEX

Multi-Select Dropdown

Accepted answers

Paul Newcome

Using the @cell reference in side of the HAS function to indicate the "where" is basically telling the formula to evaluate the previously established range {Product Line} on a cell by cell basis and look for any cell where "Arbor - COA" is selected as one of the options.

All comments

Paul Newcome

Try this:

=INDEX(COLLECT({Column Being Pulled}, {Level Selection}, @cell = [Level Selection]@row, {Feature Column}, @cell = Feature@row, {Product Line}, HAS(@cell, "Arbor - COA")), 1)

A.Czap

~~Is the "@cell" refencing something on the same page as where I would like the information to pull, I am getting an #INVALID REF error.~~

Update: I didn't change my initial reference back and the formula works perfectly. Thank you!

I still don't fully understand what the HAS is grabbing, but I am going to keep working at it.

Paul Newcome

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.