Index and Match values in a cell seperated by commas

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. MacOS
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:


Role
Proj A
Proj B
Proj C
PM
Bob
Sue
Dan
BA
Phil
Mike
Matt
SME
Ryan
Jack
Larry

<tbody>
</tbody>

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

A5
B5
C5
Gather Requirements
PM, BA, SME
Formula here

<tbody>
</tbody>

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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
It is difficult to understand what the expected result should look like

ProjectRolePerson
Proj APM,BA,SMEBob,Phil,Ryan
Proj BPM,BA,SMESue,Mike,Jack
Proj CPM,BA,SMEDan,Matt,Larry
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top