Search a string and return multiple values from another column

Question

Return Lookup Array Lookup Value    Expected Output
A   2   1   NA
B   2   2   A, B, C
C   2   3   D,E
D   3   4   F, G
E   3   5   NA
F   4   6   NA
G   4   7   NA

This is what the table looks like. The fourth column is what I expect the return values. I am using the lookup value in column 3 in column 2, the output is in column 4.

JNevill JNevill · Accepted Answer · 2015-09-21T19:40:47

Since you need multiple values returned in the same cell you are probably going to need some VBA here:

Function doLookup(lookupValue As String) As String
    Dim searchRange As Range
    Dim searchCell As Range
    Dim searchValue As String

    'What range are we searching here?
    Set searchRange = Sheet1.Range("B1:B" & Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row())

    'Loop through each cell in the range
    For Each searchCell In searchRange

        searchValue = searchCell.Value

        'If we find a match...
        If searchValue = lookupValue Then

            'If this is not the first thing in the list, then add a comma
            If doLookup <> "" Then doLookup = doLookup & ","

            'Add the thing we found
            doLookup = doLookup & searchCell.Offset(0, -1).Value
        End If
    Next searchCell
End Function

Stick this in a new module and then in D1 use the formula =DoLookup(C1) and it should spit out what you are looking for. If the list is very large, then don't expect this to be too snappy.

Search a string and return multiple values from another column

1 Answers