How would I use an Index/Collect formula to include Child Row data as one of my criterion?

For example, I would like to collect a certain column based on a specific text string in another column, however that text string is located in a child cell/row which I believe is the reason I am receiving an "Invalid Value" error. I have two separate criterion in my formula, and I removed this text string portion to isolate the issue, and as I suspected the collect formula worked in this case.

INDEX(COLLECT({Column I want to pull from},{Column with criterion 1}, cell@row criterion, {Column with criterion 2}, "text string I want to match and pull the adjacent column against"),1))



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!