Index and Match values in a cell seperated by commas

[mattyblueice]

Hi I have a huge project sheet with dozens of projects along with tasks and the associated contacts. My source list is set up like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Role
[/TD]
[TD]Proj A
[/TD]
[TD]Proj B
[/TD]
[TD]Proj C
[/TD]
[/TR]
[TR]
[TD]PM
[/TD]
[TD]Bob
[/TD]
[TD]Sue
[/TD]
[TD]Dan
[/TD]
[/TR]
[TR]
[TD]BA
[/TD]
[TD]Phil
[/TD]
[TD]Mike
[/TD]
[TD]Matt
[/TD]
[/TR]
[TR]
[TD]SME
[/TD]
[TD]Ryan
[/TD]
[TD]Jack
[/TD]
[TD]Larry
[/TD]
[/TR]
</tbody>[/TABLE]

Each tab a is project with a all of the tasks and contacts, many of the tasks require multiple roles such as

[TABLE="width: 500"]
<tbody>[TR]
[TD]A5
[/TD]
[TD]B5
[/TD]
[TD]C5
[/TD]
[/TR]
[TR]
[TD]Gather Requirements
[/TD]
[TD]PM, BA, SME
[/TD]
[TD]Formula here
[/TD]
[/TR]
</tbody>[/TABLE]

The project name is located in Cell A1, but how do I get a index/match formula to pull in the multiple roles that are seperated by commas? Any help would be appreciated!

Thanks

Matt

 

[mattyblueice]

Morning - does anyone have any ideas on this? I have been reviewing similar posts but I am unable to find one where look-up values are separated by commas and a space

 

[DanteAmor]

Hello, it is not clear if you have the data on a sheet or in several sheets.
I also do not understand if the result is on the same sheet or on another sheet.


I understand that in cell A1 you can have for example: "Proj A", so if in cell B5 you have this: "PM, BA, SME", then the result is: "Bob, Phil, Ryan".
But I do not know where to look.

Either way a single formula could not get several results, it could be with a VBA code.

 

[mattyblueice]

I think you are right - most solutions point to a VMA code which is not what I want to do. Thanks for your help, I will go with a different option!

 

[DanteAmor]

I think you are right - most solutions point to a VMA code which is not what I want to do. Thanks for your help, I will go with a different option!

It can be solved with formula, but they should be several formulas.
Good luck.

 

[sandy666]

It is difficult to understand what the expected result should look like

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Project[/td][td=bgcolor:#70AD47]Role[/td][td=bgcolor:#70AD47]Person[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Proj A[/td][td=bgcolor:#E2EFDA]PM,BA,SME[/td][td=bgcolor:#E2EFDA]Bob,Phil,Ryan[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Proj B[/td][td]PM,BA,SME[/td][td]Sue,Mike,Jack[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Proj C[/td][td=bgcolor:#E2EFDA]PM,BA,SME[/td][td=bgcolor:#E2EFDA]Dan,Matt,Larry[/td][/tr]
[/table]