COLLECT

Smartsheet-specific function that returns an array of values from one column where criteria in another column are met. Use with INDEX() to return a single value or JOIN() for a delimited string.

Syntax

smartsheet-mastery

=COLLECT(range, criteria_range, criteria, [criteria_range2, criteria2, ...])

Example

smartsheet-mastery

// Return first active project manager
=INDEX(COLLECT({PM Name}, {Status}, "Active"), 1)

// Comma-separated list of active team members
=JOIN(COLLECT([Name]:[Name], [Status]:[Status], "Active"), ", ")

// Multi-criteria: names where team is Engineering AND status is Active
=JOIN(COLLECT([Name]:[Name], [Team]:[Team], "Engineering", [Status]:[Status], "Active"), ", ")