# VLOOKUP Multiple value return in Single Cell with Repitiion

#### Jeannie11

##### New Member
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
 A B Math Geometry, Algebra, Statistics

<tbody>
</tbody>

Worksheet 2
 Math Geometry Math Algebra Math Statistics

<tbody>
</tbody>

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### sandy666

##### Well-known Member
With Power Query

 Column1 Column2 Column1 Custom Math Geometry Math Geometry,Algebra,Statistics Math Algebra Math Statistics

Code:
``````[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

##### New Member

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

##### Well-known Member
Power Query == Get&Transform 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

Last edited:

#### Jeannie11

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

#### AhoyNC

##### Well-known Member
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
AB
1MathGeometry, Algebra, Statistics
Sheet1
Excel Workbook
AB
1MathGeometry
2MathAlgebra
3HistoryHist1
4HistoryHist2
5MathStatistics
Sheet 2

#### sandy666

##### Well-known Member
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

Last edited: