Help With Index(Collect with Parent/Child Formatting

I haven't been able to pull this value using the below two formulas.. could someone lend some insight? I have to redact lots of the below unfortunately

=INDEX(COLLECT({SSFC DR}, {SSFC C}, HAS(@cell, [Cred Strip]@row), {SSFC Firm}, HAS(@cell, [Firm]@row)), 1))

=INDEX(COLLECT({SSFC DR}, {SSFC C}, CONTAINS([Cred Strip]@row, @cell), {SSFC Firm}, CONTAINS([Firm]@row, @cell)), 1))

A- SSFC DR = Yellow Dot (Value I'm trying to pull)

B- SSFC C = Orange Dot (Matches if current sheet's value contains this value)

C- SSFC Firm = Black Dot (Direct Match)

I'm pulling 5 in the below using an Index(Match based on an internal file number, then stripping to only the first word in 1.

I've even tried to join values 3 and 4- hence the HAS approach in the one formula. I don't know if its the parent/child formatting that's messing things up or if I'm just approaching this incorrectly..

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!