Index/Match vs Index/Collect vs Index/Contains

Scott_Blackburn

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?

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