excel vlookup with multiple results

Question

I am trying to use a vlookup or similar function to search a worksheet, match account numbers, then return a specified value. My problem is there are duplicate account numbers and I would like the result to concatenate the results into one string.

Acct No   CropType
-------   ---------
0001      Grain
0001      OilSeed
0001      Hay
0002      Grain  

Is in the first worksheet, on the 2nd worksheet I have the Acct No with other information and I need to get all the matching results into one column on the 2nd worksheet ie. "Grain Oilseed Hay"

Answer 1

Here is a function that will do it for you. It's a little different from Vlookup in that you will only give it the search column, not the whole range, then as the third parameter you will tell it how many columns to go left (negative numbers) or right (positive) in order to get your return value.

I also added the option to use a seperator, in your case you will use " ". Here is the function call for you, assuming the first row with Acct No. is A and the results is row B:

=vlookupall("0001", A:A, 1, " ")

Here is the function:

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional seperator As String = ", ") As String

Dim i As Long
Dim result As String

For i = 1 To lookup_column.Rows.count
    If Len(lookup_column(i, 1).text) <> 0 Then
        If lookup_column(i, 1).text = lookup_value Then
            result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
        End If
    End If
Next

If Len(result) <> 0 Then
    result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

Notes:

• I made ", " the default seperator for results if you don't enter one.
• If there is one or more hits, I added some checking at the end to make sure the string doesn't end with an extra seperator.
• I've used A:A as the range since I don't know your range, but obviously it's faster if you enter the actual range.

Answer 2

One way to do this would be to use an array formula to populate all of the matches into a hidden column and then concatenate those values into your string for display:

=IFERROR(INDEX(cropTypeValues,SMALL(IF(accLookup=accNumValues,ROW(accNumValues)-MIN(ROW(accNumValues))+1,""),ROW(A1))),"")

• cropTypeValues: Named range holding the list of your crop types.
• accLookup: Named range holding the account number to lookup.
• accNumValues: Named range holding the list of your account numbers.

Enter as an array formula (Ctrl+Shift+Enter) and then copy down as far as necessary.

Let me know if you need any part of the formula explaining.