Formula to match and pull in information across multiple rows

I want to create a formula in Sheet 1 that looks at the information in a column in Sheet 1 and searches for matches in a specific column in Sheet 2. If a match is found in that column in any of the rows in Sheet 2, the formula will pull in information from another column in all rows in Sheet 2, not just from the row of the first match.

Formula steps:

Sheet 1 - looks at information in the Facility Name column

Sheet 2 - looks to Sheet 2 to find any matches across all rows in the Facility Name column

Sheet 2 - if there's a match, looks at the Parent Name column in each row where there's a match

Sheet 1 - brings back the Parent Name column information from all matches in Sheet 2 and populates in the Parent Name column in Sheet 1 (comma separated if there are multiple results)

I don't know if anything like this is even possible, but if so would love to try out some formulas to see if they'll work here!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!