Concatenation of Index+Match results using arrays

[mmmilky]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Priority[/TD]
[TD]Responsibility[/TD]
[TD]Group Member[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DEF[/TD]
[TD]Beth[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GHI[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JKL[/TD]
[TD]Beth[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MNO[/TD]
[TD]Beth[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PQR[/TD]
[TD]Chuck[/TD]
[/TR]
</tbody>[/TABLE]
^ Example data set, where content has been changed to protect information, columns A,B,C

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Responsibility[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]ABC, GHI[/TD]
[/TR]
[TR]
[TD]Beth[/TD]
[TD]DEF, JKL, MNO[/TD]
[/TR]
[TR]
[TD]Chuck[/TD]
[TD]PQR[/TD]
[/TR]
</tbody>[/TABLE]
^ 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!!!

 

[Rada1]

Can you list responsibilities in separate cell, or they have to be in single cell?

 

[mmmilky]

I'd prefer them to be in one cell, as it is in the middle of a summary table and each member will have a different number of responsibilities.

 

[Rada1]

Well, maybe someone will have more elegant solution - this is one way to do it with fomulas, but it's not perfect.
Your example data set is in Sheet1, cells A1:C7. In columns B, C and D is separate cells solution, in column F one cell solution.

	A	B	C	D	E	F
ABC	GHI			ABC GHI
DEF	JKL	MNO		DEF JKL MNO
PQR				PQR

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Adam[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Beth[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Chuck[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=IF(COLUMNS($B2:B2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:B2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(COLUMNS($B2:C2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:C2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(COLUMNS($B2:D2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:D2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=IF(COLUMNS($B2:E2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:E2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=IF(COLUMNS($B2:B2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:B2))))&" "&IF(COLUMNS($B2:C2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:C2))))&" "&IF(COLUMNS($B2:D2)>COUNTIF(Sheet1!$C$2:$C$7,$A2),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A2,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B2:D2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]{=IF(COLUMNS($B3:B3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:B3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=IF(COLUMNS($B3:C3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:C3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=IF(COLUMNS($B3:D3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:D3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]{=IF(COLUMNS($B3:E3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:E3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]{=IF(COLUMNS($B3:B3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:B3))))&" "&IF(COLUMNS($B3:C3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:C3))))&" "&IF(COLUMNS($B3:D3)>COUNTIF(Sheet1!$C$2:$C$7,$A3),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A3,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B3:D3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]{=IF(COLUMNS($B4:B4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:B4))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=IF(COLUMNS($B4:C4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:C4))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=IF(COLUMNS($B4:D4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:D4))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]{=IF(COLUMNS($B4:E4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:E4))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]{=IF(COLUMNS($B4:B4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:B4))))&" "&IF(COLUMNS($B4:C4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:C4))))&" "&IF(COLUMNS($B4:D4)>COUNTIF(Sheet1!$C$2:$C$7,$A4),"",INDEX(Sheet1!$B$2:$B$7,SMALL(IF(Sheet1!$C$2:$C$7=$A4,ROW(Sheet1!$C$2:$C$7)-ROW(Sheet1!$C$2)+1),COLUMNS($B4:D4))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rada1]

Note: basically, you have to enter formula in B2 and copy it down and right.
Formula in F2 is made up of formulas from cells B2:D2, joined with ampersands (other way to concatenate), and copied down.