Index match with multiple value

[SAXON10]

Hi,

Index match with multiple value in same cell without duplication. In sheet 1 column A contain the range A2:A12 of code and column B contain

the range B2:B12 of text. In Sheet 2 column A&B is code and result.

I am apply the following array formula in sheet2 but I got some error.

=CHOOSE(SUMPRODUCT(--($A2=DATA!A2:$A$12)),VLOOKUP($A2,DATA!$A$2:$B$12,2,0),VLOOKUP($A2,DATA!$A$2:$B$12,2,0)&","&INDEX(DATA!$B$2:$B$12,MATCH($A2,DATA!$A$2:$A$12,0)+1),VLOOKUP($A2,DATA!$A$2:$B$12,2,0)&","&INDEX(DATA!$B$2:$B$12,MATCH($A2,DATA!$A$2:$A$12,0)+1)&","&INDEX(DATA!$B$2:$B$12,MATCH($A2,DATA!$A$2:$A$12,0)+2))

Any help much appreciated.

Sheet2

CODE	DESIRED REDULT
11-JP	LL01,LL06
5556-PP	X21,X25
888-TR	TR01,TR11

<colgroup><col><col></colgroup><tbody>
</tbody>

Sheet1

CODE	TEXT
11-JP	LL06
11-JP	LL06
11-JP	LL06
11-JP	LL01
888-TR	TR11
5556-PP	X25
5556-PP	X21
5556-PP	X21
888-TR	TR11
11-JP	LL01
888-TR	TR01

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Does your version of Excel have the TEXTJOIN function?

 

[sandy666]

alternatively you can try PowerQuery

let
    Source = Table.NestedJoin(Table2,{"CODE"},Table1,{"CODE"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"TEXT"}, {"TEXT"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table1", {"CODE"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Result", each List.Sort(List.Distinct(Table.Column([Count],"TEXT")),Order.Ascending)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"CODE", Order.Ascending}})
in
    #"Sorted Rows"

CODE	TEXT		CODE		CODE	Result
11-JP	LL06		11-JP		11-JP	LL01,LL06
11-JP	LL06		5556-PP		5556-PP	X21,X25
11-JP	LL06		888-TR		888-TR	TR01,TR11
11-JP	LL01
888-TR	TR11
5556-PP	X25
5556-PP	X21
5556-PP	X21
888-TR	TR11
11-JP	LL01
888-TR	TR01

 

[SAXON10]

Thank for the reply I don't have textjonit function. I am using Excel 2013.

 

[SAXON10]

Thanks for querry. I try and let you know. Any formula solutions !

 

[sandy666]

I prefer life easier so ... no formula

 

[Saba Sabaratnam]

Hi

I may have a formula solution for you.

Copy your data table in A1:B12, including the table headings

Enter the following formula in C2 and fill it down to C2
=COUNTIFS($A$2:A2,A2)

Enter the following array formula by pressing Shift + Control + Enter and fill it down to D12

=IF(C2=1,INDEX($B$2:$B$12,MATCH(A2,$A$2:$A$12,0)),INDEX($D1:D$1,MATCH(1,((C2-1)=$C$1:C1)*(A2=$A$1:A1),0))&";"&B2)

Copy your lookup values in A15:A17 as listed below
11-JP
5556-PP
888-TR

Enter the following formula B15 and fill it down to B17

=INDEX($D$2:$D$12,SUMPRODUCT((MAX(ROW($D$2:$D$12)*(A15=$A$2:$A$12))-1)))

Kind regards

Saba