Index/Match vs Index/Collect vs Index/Contains

Creating a sheet to collect and answer FAQs, I am attempting to populate the System/Tech Supporting SME dependent upon the selected system/technology. I have my primary collection sheet and then I have made a reference sheet for some formulas.

Relevant columns with fake data from the primary collection sheet. System/Tech is a multi-select drop down. System/Tech Supporting SME is a contact list and is where I am writing the formula for this community question.

Relevant columns with fake data from the reference sheet. System/tech categories is a text/number and system/tech contacts is a contact list.

Originally, I attempted an index match formula.

=INDEX({System/Tech Contacts}, MATCH([System/Tech Category]@row, {Reference Sheet System/Tech Categories}, 0))

It only worked for responses where a single response option was selected. It reported #NO MATCH when multiple were selected.

Reading previous Formulas and Functions posts, I believe the way to fix this short coming is to use an INDEX COLLECT or INDEX CONTAINS formula. I then started confusing myself if a JOIN or HAS formula was needed as well. I've attempted:

=INDEX(COLLECT({System/Tech Contacts}, {Support Sheet System/Tech Categories}, HAS(@cell, [System/Tech Category]@row, 1))) and get #INCORRECT ARGUMENT SET.

=INDEX(COLLECT({System/Tech Contacts}, {Support Sheet System/Tech Categories}, CONTAINS([System/Tech Category]@row, @cell), 1)) and get #INCORRECT ARGUMENT SET

Questions: How do I correct my argument set? Which formula approach is the best to use in situations like this?

Find more posts tagged with

Formulas

INDEX

Accepted answers

Paul Newcome

You need a JOIN/COLLECT actually.

=JOIN(COLLECT({Contact Column}, {Category Column}, HAS([System/Tech Category]@row, cell)), "; ")

All comments

KPH

INDEX COLLECT sounds like the right thing to do.

As for HAS or CONTAINS that will depend on your data. I am on my cell so can’t see your screenshots.

However, I can help correct the formula issue, which is the same for both.

See the ,1

That should not be part of the COLLECT. In your HAS version you need to move two ) before ,1

One will close the HAS and one to close the COLLECT. The 1 will then be the second piece of information you pass to the INDEX.

In the CONTAINS version, you did close the CONTAINS before the ,1 so only need to move one of the )

Does that make sense?

This

https://help.smartsheet.com/function/contains

and

https://help.smartsheet.com/function/has

Should help you choose between HAS and CONTAINS

Scott_Blackburn

https://community.smartsheet.com/discussion/comment/468447#Comment_468447

Thank you @KPH. Following your suggestions to move the ), I get the code to work if only a singular System/Tech Category is selected. If multiple were selected, the error code has changed to #INVALID VALUE

=INDEX(COLLECT({System/Tech Contacts}, {System/Tech Categories}, HAS(@cell, [System/Tech Category]@row)), 1)

=INDEX(COLLECT({System/Tech Contacts}, {System/Tech Categories}, CONTAINS([System/Tech Category]@row, @cell)), 1)

Paul Newcome

You need a JOIN/COLLECT actually.

=JOIN(COLLECT({Contact Column}, {Category Column}, HAS([System/Tech Category]@row, cell)), "; ")

Scott_Blackburn

Thank you @Paul Newcome . Your answers from previous community posts advised my Frankenstein formula writing. Thank you, for helping me fix it.

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