excel vlookup 有多个结果

Question

我正在尝试使用 vlookup 或类似的函数来搜索工作表，匹配帐号，然后返回指定的值。我的问题是存在重复的帐号，我希望结果将结果连接成一个字符串。

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

在第一个工作表中，在第二个工作表上，我有包含其他信息的帐号，我需要将所有匹配结果放入第二个工作表上的一列中，即。 “谷物油籽干草”

Answer 1

这是一个可以为您完成此操作的函数。它与 Vlookup 略有不同，因为您只给它搜索列，而不是整个范围，然后作为第三个参数，您将告诉它向左（负数）或向右（正数）多少列才能获得你的返回值。

我还添加了使用分隔符的选项，在您的情况下您将使用“”。下面是为您调用的函数，假设帐户编号的第一行是 A，结果是 B 行：

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

这是函数：

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

注意：

• 如果您不输入结果，我将“,”作为结果的默认分隔符。
• 如果有一个或多个命中，我在最后添加了一些检查
  确保字符串不以额外的分隔符结尾。
• 我使用 A:A 作为范围，因为我不知道你的范围，但是
  显然，如果你输入实际范围，速度会更快。

Answer 2

一种方法是使用数组公式将所有匹配项填充到隐藏列中，然后将这些值连接到字符串中以进行显示：

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

• cropTypeValues：保存作物列表的命名范围类型。
• accLookup：保存要查找的帐号的命名范围。
• accNumValues：保存您帐户列表的命名范围
  数字。

输入数组公式 (Ctrl+Shift+Enter)，然后根据需要向下复制。

如果您需要解释公式的任何部分，请告诉我。

Answer 3

我刚刚遇到了类似的问题，并且我已经查找了很长时间类似的解决方案，但没有什么真正说服我。要么你必须编写一个宏，要么编写一些特殊的函数，而对于我的需要，最简单的解决方案是使用 Excel 等数据透视表。

如果您根据数据创建一个新的数据透视表，并首先添加“Acct No”作为行标签，然后添加“CropType”作为 RowLabel，您将获得一个非常好的分组，其中列出了每个帐户的所有作物类型。但它不会在单个单元格中做到这一点。

Answer 4

这是我的代码，它甚至比 Excel vlookup 更好，因为您可以选择条件列，并且肯定还有分隔符（也可以使用回车符）...

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

还有一个礼物，您还可以对同一单元格的多个元素进行查找（基于相同的分隔符）。真的很有用，

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

上一个子需要这个功能

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

Answer 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

像这样使用：

=VLookupAll(K1, A1:C25, 3)

查找范围 A1:A25 中所有出现的 K1 值，并返回 C 列中的相应值，用 分隔逗号。

如果要对值求和，可以使用 SUMIF，例如

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

对 C1:C25 中的值求和，其中 A 列中的相应值等于K1 的值。

一切都最好。