INDEX/MATCH with multiple "Matches"

Bart P

I'm designing a project plan template and would like to use an INDEX/MATCH formula to populate a "Business Unit" column based on the staff member assigned to a particular task. Doing this would then allow me to create reports for the different Business Unit Mangers (filtered by the Business Unit column) that allow them to see all the tasks assigned to their staff across the different projects. Designing it this way means I only need to update a master list of Staff/Business Units, and not update a bunch of reports every time there's a staff change.

It works... unless more than one person is assigned to the task....

This is the formula I have in the "Business Unit" column:

=IFERROR(INDEX({Business Unit Logic BU}, MATCH([Assigned To]@row, {Business Unit Logic Staff}, 0), 1), "")

Where {Business Unit Logic BU} is a cross sheet refence to Business Units and {Business Unit Logic Staff} is the corresponding staff member.

[Assigned To] is a multi-value contact list, and things work fine as long as there is only one staff member assigned to the task. If multiple staff are assigned, then the Business Unit field is blank.

I've tried changing Business Unit to a multi-value drop-down list hoping that would be a quick fix but no luck.

Any ideas on how to get an INDEX/MATCH formula to return multiple values, or if there's a better way of doing what I'm trying to achieve?

Thanks.

Bart

Paul Newcome

You would need a JOIN/COLLECT/HAS combo instead.

=JOIN(COLLECT({Business Unit}, {Assigned To}, HAS([Assigned To]@row, @cell)), "delimiter of choice")

Paul Newcome

You would need a JOIN/COLLECT/HAS combo instead.

=JOIN(COLLECT({Business Unit}, {Assigned To}, HAS([Assigned To]@row, @cell)), "delimiter of choice")

Bart P

Thanks Paul. It looks like this will do what I need.

Paul Newcome

Happy to help. 👍️