I am looking to use an Index match to concatenate a persons roles
I have a sheet to track workers roles however, some people are listed multiple times as they have multiple roles because depending on the project their role is different. I was wondering if there was a way to use an Index(match( to either concat all of their roles in 1 formula without the use of a helper column. My current formula can only produce the first role in the list =IFERROR(INDEX(Role$3:Role$72, MATCH([Worker Name]@row, Name$3:Name$72, 0)), "")
Ex
John PM
Tina Engineer
John Engineer
For John I would only be able to pull PM as it comes first on the list. I would like to have it show for John PM,Engineer
Best Answer

Hey @Mathew Palter
The formula below will work assuming your data is arranged as I illustrated. If your data is different, please provide a screenshot.
Name Role
John PM
Tina Engineer
John Engineer
=Name@row + " " + JOIN(COLLECT(Role:Role, Name:Name, Name@row), ", ")
If you must designate absolute row numbers, edit the formula accordingly. Often with the COLLECT formula you can add criteria that would exclude unwanted rows so that you can make the formula a column formula.
Will this work for you?
Kelly
Answers

Hey @Mathew Palter
The formula below will work assuming your data is arranged as I illustrated. If your data is different, please provide a screenshot.
Name Role
John PM
Tina Engineer
John Engineer
=Name@row + " " + JOIN(COLLECT(Role:Role, Name:Name, Name@row), ", ")
If you must designate absolute row numbers, edit the formula accordingly. Often with the COLLECT formula you can add criteria that would exclude unwanted rows so that you can make the formula a column formula.
Will this work for you?
Kelly

Thank you for the help! I found a way to add a Distinct and it works perfectly!

Great! I'm glad you got the formula working to fit your purpose
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!