# Thread: VLOOKUP Multiple value return in Single Cell with Repitiion Thanks: 0 Likes: 0

1. ## VLOOKUP Multiple value return in Single Cell with Repitiion

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

Worksheet 2
 Math Geometry Math Algebra Math Statistics  Reply With Quote

2. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion

With Power Query

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

Code:
```// 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```  Reply With Quote

3. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion

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.  Reply With Quote

4. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion

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  Reply With Quote

5. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion

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

6. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion

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.

Sheet1

 A B 1 Math Geometry, Algebra, Statistics

 Cell Formula B1 {=TEXTJOIN(", ",TRUE,SUBSTITUTE(TRANSPOSE(IF(Sheet2!\$A\$1:\$A\$5=\$A1,Sheet2!\$B\$1:\$B\$5)),FALSE,""))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Sheet 2

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

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

7. ## Re: VLOOKUP Multiple value return in Single Cell with Repitiion Originally Posted by Jeannie11 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  Reply With Quote

## User Tag List

algebra, geometry, math, power query, return, worksheet 