INDEX, COLLECT, CONTAINS

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)

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!