INDEX, MATCH, CONCATENATE Problem

[RobV1]

Good evening all!

I was wondering if anyone could possible offer a solution/guidance to a problem I'm having.

I have 2 tables of data on 2 worksheets. I have 2 criteria that I wish to search, and for all matches, concatenate the values.

Unfortunately I cannot use VBA, UDFs or addins for this (IT security protocols).

The tables below should help illustrate what I'm trying to achieve.

Table 1 - where i want the concatenated values to populate:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]week[/TD]
[TD]concat info[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]week 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]week 1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2 - values to be concatenated:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]week 1[/TD]
[TD]week 1[/TD]
[TD]week 1[/TD]
[TD]week 1[/TD]
[TD]week 1[/TD]
[TD]week 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]Apples[/TD]
[TD]oranges[/TD]
[TD]pears[/TD]
[TD]apples[/TD]
[TD]pears[/TD]
[TD]pineapple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So what i'm trying to achieve is below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]week[/TD]
[TD]concat values[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]week 1[/TD]
[TD]Apples, oranges, pears, apples, pears[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]week 1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The criteria to match therefore are the name and week, with all values matching these to be concatenated as above.

I've tried a number of things but can't seem to get any to work, I'm thinking the best bet will be an index, match, concatenate combo but keep getting #value or #ref errors.

I've figured out how to use index match to pull a single value, but its the inclusion of concatenation that is throwing me.

Here is the actual formula to pull a single value:
=INDEX(Comments!$B$3:$AJ$66,MATCH($C$1,Comments!$A$2:$AJ$2,0),MATCH(A4,Comments!$A$3:$A$66,0))

Could anyone advise how to either incorporate the concatenation (i've pulled all information into one table as it really doesn't like indirect for some reason) - or what would be even better if anyone could advise how to amend it so that it will search based on an indirect range, and then concatenate the information.

Sorry its such a long post, i'm trying to make it super clear what i'm trying to achieve, its been boxing my brain for weeks and I can see no solution!

Thank you all in advance.

 

[DRSteele]

You will require the up-to-date version of Excel to use function TEXTJOIN.

Copy I7 down as necessary after properly invoking it as an array-formula.

	A	B	C	D	E	F	G	H	I
Person 1	Apples	oranges	pears	apples	pears	pineapple	grapes
Person 2	beef	veal	fois grois	lobster	oysters	caviar	sushi
Person 1	week 1
Person 2	week 1
Person 1	week 2
Person 2	week 2
person 5	week 7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Name[/TD]
[TD="bgcolor: #FFF2CC"]week 1[/TD]
[TD="bgcolor: #FFF2CC"]week 1[/TD]
[TD="bgcolor: #FFF2CC"]week 1[/TD]
[TD="bgcolor: #FFF2CC"]week 1[/TD]
[TD="bgcolor: #FFF2CC"]week 1[/TD]
[TD="bgcolor: #FFF2CC"]week 2[/TD]
[TD="bgcolor: #FFF2CC"]week 2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/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="bgcolor: #FCE4D6"]Name[/TD]
[TD="bgcolor: #FCE4D6"]week[/TD]
[TD="bgcolor: #FCE4D6"]concat values[/TD]

[TD="align: center"]7[/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="bgcolor: #E2EFDA"]Apples, oranges, pears, apples, pears[/TD]

[TD="align: center"]8[/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="bgcolor: #E2EFDA"]beef, veal, fois grois, lobster, oysters[/TD]

[TD="align: center"]9[/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="bgcolor: #E2EFDA"]pineapple, grapes[/TD]

[TD="align: center"]10[/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="bgcolor: #E2EFDA"]caviar, sushi[/TD]

[TD="align: center"]11[/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="bgcolor: #E2EFDA"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I7[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(G7=$A$2:$A$3,IF(H7=$B$1:$H$1,$B$2:$H$3,""),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]