I am looking to use an Index match to concatenate a persons roles

Mathew Palter
Mathew Palter ✭✭✭✭
in Formulas and Functions

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

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!