 Thanks:  0 Likes:  0

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

1. ## 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  Reply With Quote

2. ## 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)`  Reply With Quote

3. ## 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  Reply With Quote

4. ## Fantastic stuff.
You're a legend.

thanks  Reply With Quote

5. ## Thanks Venkat as well  Reply With Quote

6. ## Re: Vlookup - return multiple values in one cell (Concatenate?) Originally Posted by Lewiy 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  Reply With Quote

7. ## 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?  Reply With Quote

8. ## Re: Vlookup - return multiple values in one cell (Concatenate?) Originally Posted by Lewiy 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.  Reply With Quote

9. ## Re: Vlookup - return multiple values in one cell (Concatenate?) Originally Posted by nnklem 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```  Reply With Quote

10. ## Re: Vlookup - return multiple values in one cell (Concatenate?) Originally Posted by Lewiy 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.  Reply With Quote

## User Tag List

#### Posting Permissions

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