Vlookup to return Multiple values

[DShack]

Hello Excel Users,

I have a spreadsheet with multiple tabs where the first tab has unique numbers and the other tabs has the the unique numbers with multiple values associated to it. I need to have the first tab return the multiple values from the second tab. Column B in the first tab is what i would like to have displayed. Or is it possible to combine the values on the second tab by changing once it hits the next number in Column A?

First Tab:
Column A Column B
12 Returns (632, 987)
13 ETC.....
1
15
16


Second Tab:
Column A Column B
12 632
12 987
13 56
1 963
1 785
1 453
15 145
15 852
15 652
15 201
16 302
16 901
16 840

 

[alansidman]

Joined your two tables in Power Query. Here is the Mcode and results.

let
    Source = Table.NestedJoin(Table1, {"Column1"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column2"}, {"Table2.Column2"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"Column1"}, {{"Data", each _, type table [Column1=number, Table2.Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"Table2.Column2", "Index"}, {"Index.Table2.Column2", "Index.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Index",{"Data"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index.Index", type text}}, "en-US")[Index.Index]), "Index.Index", "Index.Table2.Column2"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Pivoted Column", {{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}}, "en-US"),{"1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Returns"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Column1] <> 3))
in
    #"Filtered Rows"

Book23
	A	B
1	Column1	Returns
2	1	963,785,453,
3	12	632,987,,
4	15	145,852,652,201
5	16	302,901,840,
Sheet3

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also make it easier for helpers if your gave your sample data/results with XL2BB

IF you have Excel 365 with FILTER function then

DShack 2020-08-06 1.xlsm
	A	B
1
2	12	632, 987
3	13	56
4	1	963, 785, 453
5	15	145, 852, 652, 201
6	16	302, 901, 840
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTJOIN(", ",1,FILTER(Sheet2!B$2:B$14,Sheet2!A$2:A$14=A2,""))

 

[DShack]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also make it easier for helpers if your gave your sample data/results with XL2BB

IF you have Excel 365 with FILTER function then

DShack 2020-08-06 1.xlsm
	A	B
1
2	12	632, 987
3	13	56
4	1	963, 785, 453
5	15	145, 852, 652, 201
6	16	302, 901, 840
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTJOIN(", ",1,FILTER(Sheet2!B$2:B$14,Sheet2!A$2:A$14=A2,""))

Thank you both for you help on this matter. I additionally updated my profile.

 

[DShack]

The textjoin and filter option works wonder. I appreciate both of your help on this matter. Also updated my profile.

 

[Peter_SSs]

You're welcome.

BTW, you can also use FILTER (& UNIQUE) to get the column A list in Sheet1. This only needs to be entered in cell A2 and the other results will automatically 'spill' into the other required cells.

DShack 2020-08-06 1.xlsm
	A	B
1
2	12	632, 987
3	13	56
4	1	963, 785, 453
5	15	145, 852, 652, 201
6	16	302, 901, 840
7
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=UNIQUE(FILTER(Sheet2!A2:A1000,Sheet2!A2:A1000<>""))
B2:B7	B2	=TEXTJOIN(", ",1,FILTER(Sheet2!B$2:B$14,Sheet2!A$2:A$14=A2,""))
Dynamic array formulas.