Page 1 of 3 123 LastLast
Results 1 to 10 of 21
Like Tree2Likes

Vlookup - return multiple values in one cell (Concatenate?)

This is a discussion on Vlookup - return multiple values in one cell (Concatenate?) within the Excel Questions forums, part of the Question Forums category; Hi, I have a sheet like the one below: A B 1 34 1 32 1 20 2 10 2 ...

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Posts
    175

    Default Vlookup - return multiple values in one cell (Concatenate?)

    Hi,
    I have a sheet like the one below:

    A B
    1 34
    1 32
    1 20
    2 10
    2 4
    2 9
    2 100


    I would like to use a vlookup that looks up the value in column A and returns a string of all the values in B. E.g If i was looking up 1 it would return 34 32 20 as one text string.

    I'm not too good at Arrays and Indexes etc so don't know what to use to do this.

    Any help would be greatly appreciated. Many Thanks in advance.

    Dixon

  2. #2
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,282

    Default

    Try this custom function:
    Code:
    Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim r As Range
    Dim result As String
    result = ""
    For Each r In lookuprange
        If r = lookupval Then
            result = result & " " & r.Offset(0, indexcol - 1)
        End If
    Next r
    MYVLOOKUP = result
    End Function
    Then type:
    Code:
    =MYVLOOKUP(A1,A1:A20,2)
    zerofirefox likes this.
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,822

    Default

    in C1 type 1
    and in d1 type this formula
    =IF(COUNTIF($A$1:$A$1000,$C$1)>=ROWS($1:1),INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$C$1,ROW($1:$1000)),ROW(1:1))),"")

    hit control+shift+ener
    copy D1 down the column D till you get error. (that is D1 copy and paste to D2 D3 D4 etc)

    (Frank Kable's formula
    RAM1972 likes this.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  4. #4
    Board Regular
    Join Date
    Apr 2005
    Posts
    175

    Default

    Fantastic stuff.
    You're a legend.

    thanks

  5. #5
    Board Regular
    Join Date
    Apr 2005
    Posts
    175

    Default

    Thanks Venkat as well

  6. #6
    New Member
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    3

    Question Re: Vlookup - return multiple values in one cell (Concatenate?)

    Quote Originally Posted by Lewiy View Post
    Try this custom function:
    Code:
    Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim r As Range
    Dim result As String
    result = ""
    For Each r In lookuprange
        If r = lookupval Then
            result = result & " " & r.Offset(0, indexcol - 1)
        End If
    Next r
    MYVLOOKUP = result
    End Function
    Then type:
    Code:
    =MYVLOOKUP(A1,A1:A20,2)
    Hi Lewiy,

    Based on the question and answer you gave in this post (didn't know wether to start a new post) I have the following question:

    I have a similar question regarding getting multiple results from a single VLOOKUP. My result, I want to return a Range ( B2:G2 ) and Result to a different Book as a Range Result.

    for example: I have a result file in a different book
    A B C D E F G
    4125 10 11 12 13 14 15
    4362 1 2 3 4 5 6

    I want to LOOKUP Column A (4362) and return a Range Result or an ARRAY into a different Workbook
    M N O P Q R
    1 2 3 4 5 6

    Hope you can help

  7. #7
    New Member
    Join Date
    Jun 2015
    Posts
    2

    Default Re: Vlookup - return multiple values in one cell (Concatenate?)

    Lewiy thank you for this formula, it works great. The only issue I'm having is it will return the same value in the cell if it repeats in the source document. Is there a way to modify it to only return unique values from the source?

  8. #8
    New Member
    Join Date
    Jun 2013
    Posts
    10

    Default Re: Vlookup - return multiple values in one cell (Concatenate?)

    Quote Originally Posted by Lewiy View Post
    Try this custom function:
    Code:
    Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim r As Range
    Dim result As String
    result = ""
    For Each r In lookuprange
        If r = lookupval Then
            result = result & " " & r.Offset(0, indexcol - 1)
        End If
    Next r
    MYVLOOKUP = result
    End Function
    Then type:
    Code:
    =MYVLOOKUP(A1,A1:A20,2)

    This is doing almost EXACTLY what I need and will save me so much time. Thank you for that. Can I get some help to tweak this just a little? I need it to limit the results to no more than 5 results and I also need the final result formatted so each result appears on a separate line in the same cell.

    E.g.
    Result=
    1st result
    2nd result
    3rd result
    4th result
    5th result

    All of the above in a single cell.

  9. #9
    New Member
    Join Date
    Feb 2016
    Posts
    1

    Default Re: Vlookup - return multiple values in one cell (Concatenate?)

    Quote Originally Posted by nnklem View Post
    This is doing almost EXACTLY what I need and will save me so much time. Thank you for that. Can I get some help to tweak this just a little? I need it to limit the results to no more than 5 results and I also need the final result formatted so each result appears on a separate line in the same cell.

    E.g.
    Result=
    1st result
    2nd result
    3rd result
    4th result
    5th result

    All of the above in a single cell.
    I have amended the code to fit your needs. I know this was months ago, but someone else maybe looking for the same solution.
    Code:
    Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim r As Range
    Dim result As String
    result = ""
    Dim i As Integer
    i = 0
    For Each r In lookuprange
        If r = lookupval And i <= 4 Then
            result = result & " " & r.Offset(0, indexcol - 1) & Chr(10)
            i = i + 1
        End If
        Next r
    MYVLOOKUP = result
    End Function

  10. #10
    New Member
    Join Date
    Sep 2014
    Location
    UAE
    Posts
    50

    Default Re: Vlookup - return multiple values in one cell (Concatenate?)

    Quote Originally Posted by Lewiy View Post
    Try this custom function:
    Code:
    Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim r As Range
    Dim result As String
    result = ""
    For Each r In lookuprange
        If r = lookupval Then
            result = result & " " & r.Offset(0, indexcol - 1)
        End If
    Next r
    MYVLOOKUP = result
    End Function
    Then type:
    Code:
    =MYVLOOKUP(A1,A1:A20,2)
    I dont know if i am doing something wrong.
    I have a personal macro workbook with extension xlsb. All my macros in this workbook. I opened up this workbook. Recorded macro with code by Lewiy. Saved macro.
    Now when i go to any other workbook i type =myvlookup ; and this function does not appear. Did i do something wrong.
    I can see it a saved macro in my personal workbook. Even this function is not appearing with other macros in my personal workbook.
    Also all of my other macros, saved in my personal workbook, they work in all other workbooks. This customized function does not.
    Can anyone help me.

Page 1 of 3 123 LastLast

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
  •  


DMCA.com