INDEX/MATCH formula assistance needed

Mariann Carmen
edited 07/28/23 in Formulas and Functions

Hello.

I'm using the following formula to match resources with their resource manager (=INDEX({Resource Manager2}, MATCH([Assigned To]@row, {Resource2}, 0), 1) ) and it works great for a 1:1 scenario. However, I have a many to one, or many to many scenario and I need help modifying this formula to accommodate both of them.

Is this possible, or is there a better way of achieving the same goal?

Any assistance provide will be much appreciated. Thank you.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi Mariann,

    Let's use COLLECT function for your use case as below formula:

    =JOIN(COLLECT({Resource Manager2}; {Resource2}; [Assigned to]@row); CHAR(10))



    Gia Thinh Co. - Smartsheet Solution Partner.

  • Partially. I have a many to many scenario and need to identify many resource managers to the assigned to resources. The below Join(Collect()) works for a one to one scenario, but not a many to many. Do you have a formula for a many to many scenario?


  • This formula works, but is there a way to alter it so I'm not using the Assigned To checkbox, rather, use the Resource column instead (with the potential for multiple Resources)?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Mariann Carmen

    It isn't currently possible to reference a cell with multiple values and compare that against a column that has multiple values. (The many to many scenario)

    Can you clarify what your source sheet looks like? There are ways to do the many-to-one or one-to-many, but this depends on your specific set-up.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!