VLookupwithMultipleCriteriawithVBA和陣列公式方法

因此，當需要在 VBA 中制作具有多個條件的 VLookUp 時，我們的想法是利用漂亮的陣列公式方法及其背后的想法。

問題： 我們能否將其翻譯成 VBA：

{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}

根本不使用 Excel 中的公式？例如，不這樣做：

=AGGREGATE(15, 6, '[TUR Master Report.xlsm]Archive'!$B$2:$B$13/
                  (('[TUR Master Report.xlsm]Archive'!$B$2:$B$13>=DO2)*
                   ('[TUR Master Report.xlsm]Archive'!$B$2:$B$13<=DP2)*
                   ('[TUR Master Report.xlsm]Archive'!$A$2:$A$13=A2)), 1)

或任何類似的（.ArrayFormula，.Formula，等）。

我正在考慮這樣的事情 foo = Match(1,(A1=rangeA)*(B1=rangeB)*(C1=rangeC),0)，但當然它不起作用，盡管它在Excel公式的邏輯中。到目前為止，我已經創建了以下解決方法：

Function GetLookupDataTriple(tableName As String, lookIntoColumn As String, myArray As Variant) As Variant
    
    Dim lo As ListObject
    Set lo = Sheet1.ListObjects(tableName)
    
    Dim i As Long
    For i = 2 To lo.ListColumns(myArray(0)).Range.Rows.Count
        If lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1) Then
            If lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3) Then
                If lo.ListColumns(myArray(4)).Range.Cells(RowIndex:=i) = myArray(5) Then
                    GetLookupDataTriple = lo.ListColumns(lookIntoColumn).Range.Cells(RowIndex:=i)
                    Exit Function
                End If
            End If
        End If
    Next i
    
    GetLookupDataTriple = -1
    
End Function

它與 3 個過濾器一起作業得很好，但這個想法是有點花哨的，例如在 excel 原始公式中。

uj5u.com熱心網友回復：

Select Case從 Ifs轉移到至少會使事情更干凈，以便以后添加更多標準（清潔是幻想嗎？）；只需添加新案例，而不是搞亂If和間距等。我的模型：

For i = 2 To lo.ListColumns(myArray(0)).Range.Rows.Count
    Select Case False
        Case lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1)
        Case lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3)
        Case lo.ListColumns(myArray(4)).Range.Cells(RowIndex:=i) = myArray(5)
        Case Else 
            GetLookupDataTriple = lo.ListColumns(lookIntoColumn).Range.Cells(RowIndex:=i)
            Exit For
    End Select
Next i

不過，這絕對不取決于您foo = Match(1,(A1=rangeA)*(B1=rangeB)*(C1=rangeC),0)的花哨/干凈程度。

uj5u.com熱心網友回復：

您想要一種查找 n 個條件的方法嗎？

假設以下資料：

你可以使用 XLOOKUP：

=XLOOKUP(1&1&1,A1:A9&B1:B9&C1:C9,D1:D9,"Not Found";)

這將找到 a、b 和 c = 1 且結果為 8 的最后一行

uj5u.com熱心網友回復：

如何鄰接以下內容：

Sub Macro1()
'
' Macro1 Macro
'

'
Dim myArray(5) As Variant
myArray(0) = "a"
myArray(1) = 1
myArray(2) = "b"
myArray(3) = 1
myArray(4) = "c"
myArray(5) = 1

    MsgBox (GetLookupDataTriple2("Table1", "result", myArray))
End Sub


Function GetLookupDataTriple2(tableName As String, lookIntoColumn As String, myArray As Variant) As Variant
    
    Dim lo As ListObject
    Set lo = Sheet1.ListObjects(tableName)
    
    noOfSearchParam = UBound(myArray) - LBound(myArray)
    
    Dim found As Boolean
    
    For i = 2 To lo.ListColumns(myArray(0)).Range.Rows.Count
     found = True
        For s = 0 To noOfSearchParam Step 2
            If lo.ListColumns(myArray(s)).Range.Cells(RowIndex:=i) <> myArray(s   1) Then
                 found = False
            End If
        Next s
        If found Then
            GetLookupDataTriple2 = lo.ListColumns(lookIntoColumn).Range.Cells(RowIndex:=i)
            Exit Function
        End If
    Next i
    GetLookupDataTriple2 = -1
End Function

uj5u.com熱心網友回復：

這是 match 的 paramarray 版本，它接受任意數量的相同大小或相同大小范圍的垂直陣列，并回傳相對行號：

Function myArrayMatch(ParamArray arr() As Variant) As Long
    If UBound(arr) Mod 2 <> 1 Then
        myArrayMatch = -1
        Exit Function
    End If
    Dim lgth As Long
    If TypeName(arr(LBound(arr))) = "Range" Then
        lgth = Intersect(arr(LBound(arr)).Parent.UsedRange, arr(LBound(arr))).Cells.Count
    Else
        lgth = UBound(arr(LBound(arr)))   LBound(arr(LBound(arr))) - 1
    End If
    Dim fnd() As Boolean
    ReDim fnd(1 To lgth) As Boolean

    Dim i As Long
    For i = LBound(arr) To UBound(arr) Step 2
        Dim rngarr As Variant
        If TypeName(arr(i)) = "Range" Then
            rngarr = Intersect(arr(i).Parent.UsedRange, arr(i))
        Else
            rngarr = arr(i)
        End If
        Dim j As Long
            For j = 1 To lgth
            If rngarr(j - IIf(LBound(rngarr, 1) = 0, 1, 0), 1) = arr(i   1) Then
                If i = LBound(arr) Then fnd(j) = True
            Else
                fnd(j) = False
            End If
            If i = UBound(arr) - 1 And fnd(j) Then
                myArrayMatch = j
                Exit Function
            End If
        Next j
    Next i
    
                
End Function

它可以被稱為：

relRow = myArrayMatch(ActiveSheet.Range("A:A"),"X",ActiveSheet.Range("B:B"),"Y")

范圍/垂直陣列是奇數標準，要搜索的值是偶數。

標籤：擅长 vba 查询

上一篇：在VBA中使用.Find作為范圍

下一篇：對excel表格中的行進行排序