INDEX/MATCH formula assistance needed

Mariann Carmen

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.

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))

Mariann Carmen

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?

Mariann Carmen

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.

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.