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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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)
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

Thanks Venkat as well
 

Katwan

New Member
Joined
Sep 5, 2008
Messages
3
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
 

chiguy28

New Member
Joined
Jun 10, 2015
Messages
2

ADVERTISEMENT

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

New Member
Joined
Jun 13, 2013
Messages
12
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.
 

scottieboi3000

New Member
Joined
Feb 22, 2016
Messages
1
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
 

nabeelzz

Board Regular
Joined
Sep 23, 2014
Messages
69
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top