nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

INDEX/Collect-can't get the syntax correct!

Susan L.

Hi friends,

For reasons I can't explain, for the life of me I can't correctly add the "collect" function to this formula:

=INDEX({SU26/FA26/SP27 Course Offerings Range 2}, MATCH([Assessment Course(s)]@row, {SU26/FA26/SP27 Course Offerings Range 1}))

In practice, I will be choosing multiple values from a dropdown list in the "Assessment Courses" column which need to be matched against the SU26/FA26/SP27 range 2 and return all of the stakeholders associated with those choices from the SU26/FA26/SP Course Offerings range 1 into a single cell in my target sheet. Every time I try and add "collect" I'm getting an error. I have tried asking the AI agent to fix it and also get an error essentially saying "I can't do that Dave…" Any help would be appreciated!

With thanks,

Sue

Find more posts tagged with

Accepted answers

Paul Newcome

Try this to get rid of that blank:

=JOIN(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, {SU26/FA26/SP27 Course Offerings Range 2}, @cell <> "", {SU26/FA26/SP27 Course Offerings Range 1}, HAS([Assessment Course(s)]@row, @cell)), ";")

All comments

Paul Newcome

Are you able to provide some screenshots (mock data is fine) for context?

Generally speaking, the syntax for INDEX/COLLECT is more like…

=INDEX(COLLECT({Column To Pull From}, {First Match Range}, @cell = "First Match Criteria", {Second Match Range}, @cell = "Second Match Criteria"), 1)

Susan L.

Thanks! So to confirm using my example above, my formula should look like:

=INDEX(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, MATCH([Assessment Course(s)]@row, {SU26/FA26/SP27 Course Offerings Range 1}, 1))

yes?

Susan L.

Thanks! So to confirm using my example above, my formula should look like:

=INDEX(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, MATCH([Assessment Course(s)]@row, {SU26/FA26/SP27 Course Offerings Range 1}, 1))

yes?

Here's a quick screenshot:

I have two courses under "Assessment courses" with different instructors. I need both instructors listed in the "Instructor" column. I'm matching the courses against a column in the SU26/FA26/SP27 sheet and need to populate the instructor column with the correct instructor for each course.

Paul Newcome

There is no MATCH function. Using INDEX/COLLECT would look like this:

=INDEX(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, {SU26/FA26/SP27 Course Offerings Range 1}, @cell = [Assessment Course(s)]@row), 1)

But based on your last comment with the screenshot, it sounds more like you are trying to join multiple instructors based on the selection(s) made in a multi-select dropdown? If that is the case, you need a JOIN/COLLECT instead:

=JOIN(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, {SU26/FA26/SP27 Course Offerings Range 1}, @cell = [Assessment Course(s)]@row ), ";")

And based on your screenshot, it looks like you are trying to populate multiple contacts into a single cell using this method. If that is also correct, you will need to explore one of the options listed in this other thread as (unless you have things set up a very specific way) multiple usable contacts can't be populated using a formula this way.

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

Susan L.

Thanks much! You're correct-I'm trying to add multiple contacts from a different sheet into a single cell on my sheet, based on a multi-dropdown selection within my own sheet. I've made data shuttles previously so I'm thinking that might be the route I need to go. I appreciate your time and thoughtful explanation!

Paul Newcome

@Susan L. I actually need to adjust the formula a little bit. I forgot to update for the multi-select when changing it to a JOIN.

=JOIN(COLLECT({SU26/FA26/SP27 Course Offerings Range 2}, {SU26/FA26/SP27 Course Offerings Range 1}, HAS([Assessment Course(s)]@row, @cell)), ";")

Susan L.

Holy beans! That formula worked! It's not a full "contact list" but, I can at least have the correct names pulled into a single cell which is better than I had been working with previously

Paul Newcome

Try this to get rid of that blank:

Susan L.

BINGO!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.