Concatenate Names Based on Two Factors


I'm trying to pull a list of names from a sheet based on two factors. In sheet one I need to match if a value is in a column, to use the vendor name from the column and match the vendor name in sheet 2, to pull every name assigned.

Sheet 1: If Needs Review is greater than 0, then find vendor name, and match to vendor name in sheet 2....

Sheet 2: Match Vendor Name from sheet 1, and return all names from Requested By. Although I changed the names, a vendor may have multiple names who requested information, which I need to put into a single column.

I've tried a few different ways from Collect Distinct, to Collect IFS. Right now I used a formula I found, and although I know it is not right. This is my most recent attempt:

=IF(COLLECT([Needs Review]@row <> 0, AND({Vendor}, Vendor@row, {Requested By} + {Requested By}))

What would be the right way?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!