具有多个结果的excel vlookup [英] excel vlookup with multiple results

问题描述

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

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"

推荐答案

这里有一个函数可以帮您完成.它与 Vlookup 有点不同，你只会给它搜索列，而不是整个范围，然后作为第三个参数，你会告诉它向左(负数)或向右(正数)多少列才能得到您的返回值.

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.

我还添加了使用分隔符的选项，在您的情况下，您将使用".这是您的函数调用，假设帐户编号的第一行是 A，结果是 B 行:

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, " ")

这是函数:

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 作为范围，因为我不知道你的范围，但是显然，如果您输入实际范围，它会更快.

这篇关于具有多个结果的excel vlookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！