excel vlookup with multiple results

问题:

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"

回答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.

回答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.

回答3:

I've just had a similar problem and I have looked up similar solutions for a long time, nothing really convinced me though. Either you had to write a macro, or some special function, while yet, for my needs the easiest solution is to use a pivot table in e.g. Excel.

If you create a new pivot table from your data and first add "Acct No" as row label and then add "CropType" as RowLabel you will have a very nice grouping that lists for each account all the crop types. It won't do that in a single cell though.

回答4:

Here is my code which even better than an excel vlookup because you can choose to criterie colum, and for sure a separator (Carriege return too)...

Function Lookup_concat(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        If (tableau.Cells(i, colSRC) = source) Then
            If result = "" Then
                result = tableau.Cells(i, colDST)
            Else
                result = result & separator & tableau.Cells(i, colDST)
            End If
        End If
    Next
    Lookup_concat = result
End Function

And a gift, you can make also a lookup on multiple element of the same cell (based on the same separator). Really usefull

Function Concat_Lookup(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    Dim Splitted As Variant

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    Splitted = split(source, separator)

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        For Each word In Splitted
            If (tableau.Cells(i, colSRC) = word) Then
                If result = "" Then
                    result = tableau.Cells(i, colDST)
                Else
                    Dim Splitted1 As Variant
                    Splitted1 = split(result, separator)
                    If IsInArray(tableau.Cells(i, colDST), Splitted1) = False Then
                        result = result & separator & tableau.Cells(i, colDST)
                    End If
                End If
            End If
        Next
    Next
    Concat_Lookup = result
End Function

Previous sub needs this function

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

回答5:

Function VLookupAll(vValue, rngAll As Range, iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
        If rCell.Value = vValue Then
            VLookupAll = VLookupAll & sSep & rCell.Offset(0, iCol - 1).Value
        End If
    Next rCell
    If VLookupAll = "" Then
        VLookupAll = CVErr(xlErrNA)
    Else
        VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
    End If
ErrHandler:
    If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
End Function

Use like this:

=VLookupAll(K1, A1:C25, 3)

to look up all occurrences of the value of K1 in the range A1:A25 and to return the corresponding values from column C, separated by commas.

If you want to sum values, you can use SUMIF, for example

=SUMIF(A1:A25, K1, C1:C25)

to sum the values in C1:C25 where the corresponding values in column A equal the value of K1.

ALL D BEST.