VLOOKUP Multiple value return in Single Cell with Repitiion

[Jeannie11]

Hello -

I have a reference value that I'm looking to return the associated values into one cell but am not sure how to do that. For example, assume the reference value is MATH in worksheet 1 and in Worksheet 2 Math is associated with Geometry, Algebra, Statistics. I'd like to return both Geometry and Algebra in one cell in worksheet 1, and separate by commas.

Worksheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Math[/TD]
[TD]Geometry, Algebra, Statistics[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Math[/TD]
[TD]Geometry[/TD]
[/TR]
[TR]
[TD]Math[/TD]
[TD]Algebra[/TD]
[/TR]
[TR]
[TD]Math[/TD]
[TD]Statistics[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance!!

 

[sandy666]

With Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Math[/td][td=bgcolor:#DDEBF7]Geometry[/td][td][/td][td=bgcolor:#E2EFDA]Math[/td][td=bgcolor:#E2EFDA]Geometry,Algebra,Statistics[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Math[/td][td]Algebra[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Math[/td][td=bgcolor:#DDEBF7]Statistics[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Group, "Custom", each List.Distinct(Table.Column([Count],"Column2"))), {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract[/SIZE]

 

[Jeannie11]

Thank you for replying!

I'm not familiar with what Power Query is and not seeing an icon for it on the Excel. Could you please guide me as to what that means and where I place this code? Assuming I'd need to tweak the code as well so that it looks to the right cells in my spreadsheet.

 

[sandy666]

Power Query == Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

PQ works with Tables
select your range, choose From Table and it will open PQ Editor then copy code from the post, open Advanced Editor and replace code there with copied code
be sure your table headers are the same as in my post

 

[Jeannie11]

Ok! I may be doing something wrong because I keep receiving an error... "column1 on the table is not found".

 

[AhoyNC]

If you have Excel 2016 or later with the TEXTJOIN function then here is another option.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B
1	Math	Geometry, Algebra, Statistics
Sheet1

Excel Workbook
	A	B
1	Math	Geometry
2	Math	Algebra
3	History	Hist1
4	History	Hist2
5	Math	Statistics
Sheet 2

 

[sandy666]

Ok! I may be doing something wrong because I keep receiving an error... "column1 on the table is not found".

you need to check headers, Column1 is not the same as column1 or Column 1

example