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

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top