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

 

[olliecottam]

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)

Lewiy

This code is great (and I see 9 years old so possibly unlikely to get a reply...!).

I've been trying to adapt it to work on a filter or array. Basically I need the lookuprange to be an array based on the data being filtered from another IF formula (I was thinking IF($C$2:$C$20000=A35,$I$2:$I$20000,"")) but I'm afraid my non-existent knowledge of VBA can't help me edit the code to work with an array. Are you able to help?

Thanks

 

[Ady.]

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)

Lewiy, 10 YEAR ON! still helping people... Thank you...

 

[SudoUK]

Hi, wondering if anyone can help me, please!
Looking for a VLOOKUP solution for multiple rows with same lookup value.


I have Sheet1 with details of all the Jobs I've done.
Job ID, Date, Pay... etc

On Sheet2, I'm trying to add up total pay per day.
Date, Number of jobs, Total Pay...

I used DCOUNT to pickup total jobs per date.

(I have now converted date into integar.)
I can use Vlookup to return single result...
=IF(E3>0;VLOOKUP (C3;Sheet1.C1:Sheet1.AK534;18;0) ;0)
if job>0, lookup date; in Sheet1; return column 18, etc

Have tried SUM with VLOOKUP, but lookup returns first value only and then keeps adding that multiple times!

Had a look at Macro solution... I'm lost, it's been 25 years since school, struggling to make sense of it all!!!

Any help much appreciated, many thanks.

 

[jtakw]

Hi,

Sounds like possibly SUMPRODUCT might help.

Search for "Excel Sumproduct" for resources and explanations.

 

[EricB_Tor]

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

I'm sorry to bump such an old thread, but this code is fantastic and I just was wondering if there is a way to modify it so that the function does not return multiple results? I have a data file that outlines what truck model a specific part is used on. The part is used numerous times on the same truck model, and this code is returning all of those results. I just need the initial, unique result.

Is that possible?