INDEX MATCH (Multiple Criteria) vs INDEX COLLECT

NiAlex

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!

heyjay

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

HAS is used for cells with multiple dropdown. Can you try to replace that with CONTAINS? What error are you having?

NiAlex

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)))

heyjay

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)

NiAlex

hi @heyjay thank you for the response, unfortunately, this formula still receives an #INCORRECT ARGUMENT error

heyjay

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)

NiAlex

@heyjay thank you! That worked. What is the significance of adding the "1" as a row_index?