Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Thank you in advance!!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Multiple value return in Single Cell with Repitiion

    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.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    Last edited by sandy666; Sep 29th, 2019 at 12:26 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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".

  6. #6
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,276
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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

    AB
    1MathGeometry, Algebra, Statistics

    Spreadsheet Formulas
    CellFormula
    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

    AB
    1MathGeometry
    2MathAlgebra
    3HistoryHist1
    4HistoryHist2
    5MathStatistics


    Excel tables to the web >> Excel Jeanie HTML 4

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP Multiple value return in Single Cell with Repitiion

    Quote Originally Posted by Jeannie11 View Post
    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 by sandy666; Sep 29th, 2019 at 09:18 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •