INDEX, COLLECT, CONTAINS

mtk5200

I'm trying to pull a specific number out of another SmartSheet based on it matching information in one specific cell. I don't know if Index/Collect is the best way to do that but that's what I've been trying. The criteria is Description 2 being somewhere in the cell of Budget Range 1, Description 4 being somewhere in Budget Range 1, and the GL Acct Code matching Budget Range 3. All of these columns are just text columns.

Budget Range 2 - The number to bring back

Budget Range 1 - The column that includes a string of text like "DP340 ALL 2023" or "DP341 Q1 2023"

Description 2 - Can contain things like "DP340" or "DP341"

Description 4 - Can contain things like "All 2023" or "Q1 2023"

GL Acct Code - will contain budget codes like "65000-01"

Budget Range 3 - will contain budget codes like "65000-01"

I can't just put the text in the formula because i want to change the DP and have all of the formulas adjust from there.

My Formula:

=INDEX(COLLECT({Budget Range 2}, {Budget Range 1}, CONTAINS($Description$2, @row, ""), {Budget Range 1}, CONTAINS($Description$4, @row, ""), {Budget Range 3}, [GL Acct Code] @row), 1)

Paul Newcome

You need to use "@cell" in the CONTAINS functions and remove the space before that third "@row".

=INDEX(COLLECT({Budget Range 2}, {Budget Range 1}, CONTAINS($Description$2, @cell), {Budget Range 1}, CONTAINS($Description$4, @cell), {Budget Range 3}, [GL Acct Code]@row), 1)

Paul Newcome

You need to use "@cell" in the CONTAINS functions and remove the space before that third "@row".

=INDEX(COLLECT({Budget Range 2}, {Budget Range 1}, CONTAINS($Description$2, @cell), {Budget Range 1}, CONTAINS($Description$4, @cell), {Budget Range 3}, [GL Acct Code]@row), 1)

mtk5200

That worked! Thanks!

Paul Newcome

Happy to help. 👍️