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

[TABLE="width: 209"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CODE[/TD]
[TD]DESIRED REDULT[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL01,LL06[/TD]
[/TR]
[TR]
[TD]5556-PP[/TD]
[TD]X21,X25[/TD]
[/TR]
[TR]
[TD]888-TR[/TD]
[TD]TR01,TR11[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1

[TABLE="width: 150"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CODE[/TD]
[TD]TEXT[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL06[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL06[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL06[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL01[/TD]
[/TR]
[TR]
[TD]888-TR[/TD]
[TD]TR11[/TD]
[/TR]
[TR]
[TD]5556-PP[/TD]
[TD]X25[/TD]
[/TR]
[TR]
[TD]5556-PP[/TD]
[TD]X21[/TD]
[/TR]
[TR]
[TD]5556-PP[/TD]
[TD]X21[/TD]
[/TR]
[TR]
[TD]888-TR[/TD]
[TD]TR11[/TD]
[/TR]
[TR]
[TD]11-JP[/TD]
[TD]LL01[/TD]
[/TR]
[TR]
[TD]888-TR[/TD]
[TD]TR01[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]CODE[/td][td=bgcolor:#5B9BD5]TEXT[/td][td][/td][td=bgcolor:#5B9BD5]CODE[/td][td][/td][td=bgcolor:#70AD47]CODE[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]11-JP[/td][td=bgcolor:#DDEBF7]LL06[/td][td][/td][td=bgcolor:#DDEBF7]11-JP[/td][td][/td][td=bgcolor:#E2EFDA]11-JP[/td][td=bgcolor:#E2EFDA]LL01,LL06[/td][/tr]

[tr=bgcolor:#FFFFFF][td]11-JP[/td][td]LL06[/td][td][/td][td]5556-PP[/td][td][/td][td]5556-PP[/td][td]X21,X25[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]11-JP[/td][td=bgcolor:#DDEBF7]LL06[/td][td][/td][td=bgcolor:#DDEBF7]888-TR[/td][td][/td][td=bgcolor:#E2EFDA]888-TR[/td][td=bgcolor:#E2EFDA]TR01,TR11[/td][/tr]

[tr=bgcolor:#FFFFFF][td]11-JP[/td][td]LL01[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]888-TR[/td][td=bgcolor:#DDEBF7]TR11[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]5556-PP[/td][td]X25[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]5556-PP[/td][td=bgcolor:#DDEBF7]X21[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]5556-PP[/td][td]X21[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]888-TR[/td][td=bgcolor:#DDEBF7]TR11[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]11-JP[/td][td]LL01[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]888-TR[/td][td=bgcolor:#DDEBF7]TR01[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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