Index Collect Contains Formula
Hi, I'm trying to report data in one cell if data in another cells contains a partial phrase listed in a third cell. Here's what I've got:
=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)))
It's giving me #incorrect argument. The reason I need a Contains is because the primary column is referencing someone's name, but in Pilot/PM he's listed as Person 1/Person 2. If Person 1 appears in Pilot/PM, I want it to report out the data from WE Org Owner for that row.
What am I missing?
Best Answer
-
Looks like you're just missing the row index portion of the index function!
Try this:
=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)), 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Looks like you're just missing the row index portion of the index function!
Try this:
=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)), 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Perfect, thank you!!
-
No problem! I'm glad that worked for you.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I am in need of the same formula I have looked at it for 15 min now and I cant figure out what I have wrong, would you mind looking to see if I have a syntax error in this?
=INDEX(COLLECT({Cert Import Temp Range 1}, {Cert Import Temp Range 2}, CONTAINS([Primary Column]@row)), 1)
-
Hi @Bwoods113
Happy to help 🙂
It's the CONTAINS function that's missing a piece. You need an @cell in there to tell it to check through each cell of the previously stated reference, like so:
=INDEX(COLLECT({Cert Import Temp Range 1}, {Cert Import Temp Range 2}, CONTAINS([Primary Column]@row, @cell)), 1)
That should do the trick!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much I appreciate all your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!