Hello all,
I'm working in excel 2010 and have been struggling trying to concatenate results to an Index+Match formula. I was forced to use Index+Match instead of Vlookup as the column that I am searching for data in is to the right of the column that I'd like to extract data from.
I'm am creating a status summary page for a project progress tracking workbook. The data set is organized by priority (subjective) and contains responsibilities and an assignee. I would like to create a status summary page that will show me the responsibilities of all group members in the following format "ABC, DEF, GHI" (where the acronyms are responsibilities).
<tbody>
</tbody>^ Example data set, where content has been changed to protect information, columns A,B,C
<tbody>
</tbody>^ Desired output, columns A,B
I have run a formula to extract a unique list of names from the whole data set. I used the formula below in cell A2.
=INDEX('Project_Tracking.xlsb'!Names, MATCH(0, COUNTIF($A$1:A1, 'Project_Tracking.xlsb'!Names&""), 0))
Where "Names" is a named list of the data set of the column that holds all the responsible project members.
I just can't figure out how the array formula to solve my problem.
So far this is all I've come up with.
{=CONCATENATE(INDEX(Responsibility,MATCH("Adam",Names,0)))}
note: the summary and data set are on different pages.
Also, please attempt to provide an answer using formulas, as I am not experienced with VBA macros.
Help would be appreciated, thanks for your time and help!!!
I'm working in excel 2010 and have been struggling trying to concatenate results to an Index+Match formula. I was forced to use Index+Match instead of Vlookup as the column that I am searching for data in is to the right of the column that I'd like to extract data from.
I'm am creating a status summary page for a project progress tracking workbook. The data set is organized by priority (subjective) and contains responsibilities and an assignee. I would like to create a status summary page that will show me the responsibilities of all group members in the following format "ABC, DEF, GHI" (where the acronyms are responsibilities).
| Priority | Responsibility | Group Member |
| 1 | ABC | Adam |
| 2 | DEF | Beth |
| 3 | GHI | Adam |
| 4 | JKL | Beth |
| 5 | MNO | Beth |
| 6 | PQR | Chuck |
<tbody>
</tbody>
| Name | Responsibility |
| Adam | ABC, GHI |
| Beth | DEF, JKL, MNO |
| Chuck | PQR |
<tbody>
</tbody>
I have run a formula to extract a unique list of names from the whole data set. I used the formula below in cell A2.
=INDEX('Project_Tracking.xlsb'!Names, MATCH(0, COUNTIF($A$1:A1, 'Project_Tracking.xlsb'!Names&""), 0))
Where "Names" is a named list of the data set of the column that holds all the responsible project members.
I just can't figure out how the array formula to solve my problem.
So far this is all I've come up with.
{=CONCATENATE(INDEX(Responsibility,MATCH("Adam",Names,0)))}
note: the summary and data set are on different pages.
Also, please attempt to provide an answer using formulas, as I am not experienced with VBA macros.
Help would be appreciated, thanks for your time and help!!!
