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

问题描述

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

 函数MYVLOOKUP （pValue As String，pWorkRng As Range，pIndex As Long）
'更新20150310 
 Dim rng As Range 
 Dim xResult As String 
 xResult =
 For Each rng在pWorkRng 
如果rng = pValue然后
 xResult = xResult& & rng.Offset（0，pIndex  -  1）
结束如果
下一个
 MYVLOOKUP = xResult 
结束函数
  

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

任何想法如何升级？

谢谢。
更新如下：

数据表：

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

解决方案

这是一个略有不同的方法。
$ b

 函数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 
如果TypeName（arr）=Range然后
 arr2 = arr.Value 
 Else 
 arr2 = arr 
 End If 
 On Error Resume Next 
t = UBound（arr2，2）
y = UBound（arr2， 1）
 On Error GoTo 0 
 
如果t> = 0而y> = 0则
对于c = LBound（arr2，1）To UBound（arr2，1 ）
对于d = LBound（arr2，1）到UBound（arr2,2）
如果arr2（c，d） 还是不skipblank然后
 TEXTJOIN = TEXTJOIN& arr2（c，d）&如果arr2（c）< ;> 还是不skipblank然后
 TEXTJOIN = TEXTJOIN& arr2（c）& delim 
 End If 
 Next c 
 End If 
 TEXTJOIN = Left（TEXTJOIN，Len（TEXTJOIN） -  Len（delim））
结束函数
  

它允许您确定如果您可以具有，或只是一个空格或任何你想放在返回值之间的东西。

第二个条件询问你是否要为任何空的空格返回一个空格。

第三个将使用IF（）的数组形式，使用要过滤返回值的条件。

所以在你的实例中，你将使用数组形式：

  = TEXTJOIN（，TRUE，IF （A2：A7 =A）*（B2：B7 = 2），C2：C7，））
  

表示我们想要在值之间有一个空格。

TRUE 意味着我们跳过任何空格，这是重要的，因为我们发送空格，当值没有被过滤器证明。

code> IF（（A2：A7 =A ）*（B2：B7 = 2），C2：C7，）循环遍历列，并在两个布尔测试为TRUE时返回值，否则返回空白。 >

存在和数组公式时，退出编辑模式而不是Enter时，必须使用Ctrl-Shift-Enter确认。如果正确完成，Excel将在公式周围放置 {} 。

---

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

  = TEXTJOIN（，TRUE，C2：C7）
  

以一般的形式，它将在一个单元格中返回 8 3 3 9 2 3 。

---

注意

Office 365 Excel TEXTJOIN 是一种在这两种情况下都输入的公式。

使用上述公式，不​​使用vb​​a代码。

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

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

Any ideas how this could be upgraded?

Thanks. Update below:

Data table:

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 if the delimeter, 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.

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

So in you 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.

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

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.

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)

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

---

NOTE

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

You would simply use the formulas as described above and not use the vba code.

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