INDEX MATCH (Multiple Criteria) vs INDEX COLLECT
Hello Smartsheet Community,
I have a metric sheet set up where I am pulling from a much larger budget sheet - on my metric sheet, I want to pull in the market and due dates based on the project description.
Nearly all projects have a unique description and my INDEX MATCH function works without issue; however, there are a few projects with very generic names that have multiple matches on the source budget sheet.
In these cases, I am trying an INDEX MATCH function with multiple criteria or an INDEX COLLECT, but have been unable to get the formula to work so far.
Original INDEX MATCH function (works):
=INDEX({2025 Capital Budget - Con/Fac Broad Scope Due Date}, MATCH([Project Description]@row, {2025 Capital Budget - Con/Fac Project Description}, 0))
Attempted INDEX COLLECT function, where I added "Community" as an additional criteria:
=INDEX(COLLECT({2025 Capital Budget - Con/Fac Market}, {2025 Capital Budget - Con/Fac Project Description}, HAS(@cell, [Project Description]@row), {2025 Capital Budget - Con/Fac Community}, HAS(@cell, Community@row)))
Attempted INDEX MATCH function with multiple criteria:
=INDEX({2025 Capital Budget - Con/Fac Market}, MATCH([Project Description]@row, {2025 Capital Budget - Con/Fac Project Description}, 0), MATCH([Community]@row, {2025 Capital Budget - Con/Fac Community}, 0))
Thank you in advance to the community!
Best Answer
-
How bout this?
=INDEX(COLLECT( {2025 Capital Budget - Con/Fac Market}, {2025 Capital Budget - Con/Fac Project Description}, CONTAINS([Project Description]@row, @cell), {2025 Capital Budget - Con/Fac Community}, CONTAINS(Community@row, @cell)), 1)
...
Answers
-
HAS is used for cells with multiple dropdown. Can you try to replace that with CONTAINS? What error are you having?
...
-
hi @heyjay when I change HAS to CONTAINS, I receive an "INCORRECT ARGUMENT" error:
=INDEX(COLLECT({2025 Capital Budget - Con/Fac Market}, {2025 Capital Budget - Con/Fac Project Description}, CONTAINS(@cell, [Project Description]@row), {2025 Capital Budget - Con/Fac Community}, CONTAINS(@cell, Community@row)))
-
The based on the documentation, the @cell should be the second arguement in the CONTAINS function. Can you please try this:
=INDEX(COLLECT( {2025 Capital Budget - Con/Fac Market}, {2025 Capital Budget - Con/Fac Project Description}, CONTAINS([Project Description]@row, @cell), {2025 Capital Budget - Con/Fac Community}, CONTAINS(Community@row, @cell)
...
-
hi @heyjay thank you for the response, unfortunately, this formula still receives an #INCORRECT ARGUMENT error
-
How bout this?
=INDEX(COLLECT( {2025 Capital Budget - Con/Fac Market}, {2025 Capital Budget - Con/Fac Project Description}, CONTAINS([Project Description]@row, @cell), {2025 Capital Budget - Con/Fac Community}, CONTAINS(Community@row, @cell)), 1)
...
-
@heyjay thank you! That worked. What is the significance of adding the "1" as a row_index?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!