Find cells matching a criteria and return combined results
Hi,
New to Smartsheet and hitting a roadblock in one of my formulas.
I have two sheets: Projects and Resources. On the Projects sheet I collect resource groups that will work on a project. Each project in a row might have either single or multiple resource groups. On the Resources sheet I have resource group names alongside the email addresses for those resource group owners. Each resource group in a row may have single or multiple email contacts.
I want to add a column in Projects that looks at the resource group in a row and returns all the email contacts.
I can do index/match, but only gives me one result. I'm not sure how to find/combine the results from all the matches. Image below and attachment with sample data.
Thanks for any help!
Answers
-
To find cells matching specific criteria and return combined results, we can utilize functions that search for data within a range based on defined conditions, consolidating the outputs into a single, summarized result. For example, in spreadsheet software like Excel or Google Sheets, functions such as
FILTER
,IF
,VLOOKUP
, orINDEX
in combination withMATCH
can locate cells that meet certain criteria and gather them in a structured format. -
Thanks for the reply zamirmewelldy, Do you have any thoughts on how to structure the formula in Smartsheet? These are common formulas, I agree, and you seem to grasp the type of problem I'm facing.
If I confused you, I only attached example data in excel format because it was easy for me to do this and it can be easily copied/pasted into a Smartsheet by someone wanting a simple dataset to work with. I'm not actually using Excel or Google sheets for this problem.
-
Not sure how many Resource Groups you have it requires a cell reference for each group but does work. The challenges is having both of them multi select cells
=JOIN(COLLECT({Resource Group Lead Fruit}, CONTAINS("Fruit", [Resource Group]@row), 1), ",") + "," + JOIN(COLLECT({Resource Group Lead Cereal}, CONTAINS("Cereal", [Resource Group]@row), 1), ",") + "," + JOIN(COLLECT({Resource Group Lead Candy}, CONTAINS("Candy", [Resource Group]@row), 1), ",")
-
Hi Hollie, I tried that formula but I couldn't get it to work for me. So I tried just using one line of it and still no luck. I had to modify it a bit to work with the my dataset.
=JOIN(COLLECT([Resource Group Lead]1:[Resource Group Lead]3, CONTAINS("Fruit", [Resource Group]@row, 1)), ",")
I get an #Incorrect argument set error.
Any thoughts why?
-
Your parentheses are in the wrong place.
=JOIN(COLLECT([Resource Group Lead]1:[Resource Group Lead]3, CONTAINS("Fruit", [Resource Group]@row), 1), ",")
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Here is another solution using @cell instead of "Fruit", "Cereal", etc.
Formula:
=JOIN(COLLECT({Resource : Group Lead}, {Resource : Group}, CONTAINS(@cell, [Resource Group]@row)), ", ")
Explanation:
This formula retrieves and combines email contacts from the "Resources" sheet based on matching resource groups in the "Projects" sheet. Let's examine how each part works:
- By using
COLLECT
with this setup, the formula gathers all email addresses from{Resource : Group Lead}
that correspond to any matching resource groups in[Resource Group]@row
.COLLECT
: This function gathers all matching values from a specified range based on given criteria.{Resource : Group Lead}
: This is the range in the "Resources" sheet where the email addresses are stored.{Resource : Group}
: This is the range in the "Resources" sheet where the resource group names are listed.CONTAINS(@cell, [Resource Group]@row)
: This criterion checks if the resource group(s) listed in the current row of the "Projects" sheet (i.e.,[Resource Group]@row
) is present in each entry of the{Resource : Group}
column.- @cell : Refers to each individual cell in the
{Resource : Group}
column asCOLLECT
goes through the list. [Resource Group]@row
: Refers to the value in the "Resource Group" column for the current row in the "Projects" sheet.- The
CONTAINS
function allows this to work with multiple resource groups listed in one cell, matching any that are present in{Resource : Group}
.
- @cell : Refers to each individual cell in the
JOIN(..., ", ")
:- After
COLLECT
retrieves all relevant email addresses,JOIN
combines these results into a single cell. - The
", "
parameter inJOIN
specifies that each email address should be separated by a comma and space for readability.
- After
- By using
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 214 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 457 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!