Excel- Return multiple value in one cell from multiple rows

Question

I try to VLOOKUP but it can only returns 1 value. I want my result to return multiple email and my lookup value is the multiple names in one cell. The first cell is the value I want to match with my lookup table.

Below is my lookup table where everybody email is here, but how I achieve the result like [email protected];[email protected], so I can just automate a reminder email in VBA using lookup emails earlier? How I can achieve this?

I also got this error when I debug, I cannot get the selected emails

Sub getEmails()
Dim toNames As Range
Set toNames = Range("J3:J500") ' names input by user

Dim names As Range
Set names = Range("Email!B3:C25") ' names range from lookup table from different worksheet

Dim splitNames
splitNames = Split(toNames, ",")

Dim selectedEmails As String

For i = 0 To UBound(splitNames)
    findRange = names.Find(What:=splitNames(i), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    ' if match found, get the email and store to selected emails variable
    If Not findRange Is Nothing Then
    selectedEmails = selectedEmails & Range("B" & findRange.Row) & ";"
    End If


Next i

'output emails
Range("Q3:Q500") = selectedEmails
End Sub

That was exactly I don't want to do. I cannot predict what name the staff gonna write in the future. So I need to have a formula to lookup the value — gpsrosak
I would recommend you just do multiple VLookups within a loop in VBA. So, if Pui Kuan, Eric is the input field, Split it into individual names, do VLookups on each of the individual names, and concatenate the results. — YowE3K
Can you show me the example based on VBA that I have scripted above? Thanks — gpsrosak

nightcrawler23 nightcrawler23 · Accepted Answer · 2016-12-05T03:25:12

So you want to enter a set of names in a cell and get the corresponding emails from the table in another cell. Try the below code. I have assumed that the names were entered separated by commas and the resulting emails are separated by ";"

Sub getEmails()
        Dim toNames As Range
        Set toNames = Range("D25") ' names input by user

        Dim names As Range
        Set names = Range("A25:A39") ' names range from lookup table

        Dim splitNames
        splitNames = Split(toNames, ",")

        Dim selectedEmails As String
        Dim findRange As Range

        For i = 0 To UBound(splitNames)
            ' find the range matching the name
            findRange = names.Find(What:=splitNames(i), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

           ' if match found, get the email and store to selected emails variable
            If Not findRange Is Nothing Then
                selectedEmails = selectedEmails & Range("B" & findRange.Row) & ";"
            End If

        Next i

        'output emails
        Range("D26") = selectedEmails
    End Sub

Also this will show run time error if any of the names isn't found in the list.

Excel- Return multiple value in one cell from multiple rows

1 Answers