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

[dixon1983]

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

 

[Lewiy]

Try this custom function:

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:

=MYVLOOKUP(A1,A1:A20,2)

 

[venkat1926]

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

 

[dixon1983]

Fantastic stuff.
You're a legend.

thanks

 

[dixon1983]

Thanks Venkat as well

 

[Katwan]

Try this custom function:

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:

=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

 

[chiguy28]

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?

 

[nnklem]

Try this custom function:

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:

=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.

 

[scottieboi3000]

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.

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

 

[nabeelzz]

Try this custom function:

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:

=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.