具有多个条件的 VLOOKUP 在一个单元格中返回值 [英] VLOOKUP with multiple criteria returning values in one cell

问题描述

我发现这个 VBA 能够使用一个匹配条件将所有匹配的值返回到一个单元格中:

I found this VBA that is capable to return all matching values into one cell using one criteria to match:

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & " " & rng.Offset(0, pIndex - 1)
    End If
Next
MYVLOOKUP = xResult
End Function

但是我需要这个 VLOOKUP 来返回与多个匹配条件相比的值.

But I need this VLOOKUP to return values compared to multiple matching criteria.

有什么想法可以升级吗?

Any ideas how this could be upgraded?

谢谢.更新如下:

数据表:

我需要公式来返回 A1-1A 和 A.0002 匹配的单元格中的值.结果应该是8 3

I need formula to return values in one cell where A1-1A and A.0002 matches. Outcome should be 8 3

推荐答案

这里有一个稍微不同的方法.

Here is a slightly different approach.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0
    
    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

它允许您确定分隔符，因为您可以使用 、 或只是一个空格或您想在返回值之间放置的任何内容.

It allows you to determine the delimiter, as in you can have , or just a space or anything you want to put between the return values.

第二个条件询问您是否要为任何空的空间返回一个空的空间.

The second criteria asks if you want to return an empty space for any that are empty.

第三个你会放置一个 IF() 的数组形式，它使用你想要过滤返回值的条件.

The third you would put an array form of IF() that uses the criteria you want to filter the return values.

因此在您的实例中，您将以数组形式使用它:

So in your instance you would use this in array form:

=TEXTJOIN(" ",TRUE,IF((A2:A7="A")*(B2:B7=2),C2:C7,""))

<代码>"" 表示我们希望值之间有一个空格.

The " " says we want a space between the values.

TRUE 意味着我们跳过任何空白，这很重要，因为当过滤器未验证值时我们发送空白.

The TRUE means we skip any blanks, this is important as we send blanks when the values are not justified by the filter.

IF((A2:A7=A")*(B2:B7=2),C2:C7,") 循环遍历列并在以下情况下返回值两个布尔测试都是 TRUE，否则返回空白.

the IF((A2:A7="A")*(B2:B7=2),C2:C7,"") cycles through the columns and returns the values when both Boolean tests are TRUE, If not it returns a blank.

在退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认和数组公式.如果操作正确，Excel 会将 {} 放在公式周围.

Being and array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done correctly then Excel will put {} around the formula.

如果你想返回完整的列，你可以简单地使用:

If you wanted to return the full column you could simply use:

=TEXTJOIN(" ",TRUE,C2:C7)

在常规形式中，它会在一个单元格中返回 8 3 3 9 2 3.

In regular form and it would return 8 3 3 9 2 3 in one cell.

注意

如果您有 Office 365 Excel TEXTJOIN 是一个本机存在的公式，在两种情况下都像上面一样输入.

If you have Office 365 Excel TEXTJOIN is a formula that exists natively that is entered like above in both cases.

Office 365 也有 FILTER，我们可以使用:

Office 365 also has FILTER and we can use:

=TEXTJOIN(" ",TRUE,FILTER(C2:C7,(A2:A7="A")*(B2:B7=2),""))
 

这篇关于具有多个条件的 VLOOKUP 在一个单元格中返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！