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?

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!