How to create UDF for multi-variable Index Match f

问题:

I want to create a UDF for the following Excel index-match formula:

{=INDEX($A$1:$J$7,MATCH(B9,$A$1:$A$7,0),MATCH(1,($A$1:$J$1=B10)*($A$2:$J$2=B11),0))}

enter image description here

    AA  AA  AA  BB  BB  BB  CC  CC  CC
    a   b   c   a   b   c   a   b   c
1   10  55  24  48  95  19  5   28  65
2   16  48  3   62  46  50  59  80  17
3   63  47  19  23  67  26  14  16  9
4   55  91  4   55  72  79  27  39  50
5   75  53  7   42  45  19  58  41  12

Condition1  3                               
Condition2  AA                              
Condition3  c                               

index-Match 19  =INDEX($A$1:$J$7,MATCH(B9,$A$1:$A$7,0),MATCH(1,($A$1:$J$1=B10)*($A$2:$J$2=B11),0))}                         

NOTE: CTRL + SHIFT + ENTER to return array result in excel

This works in an excel worksheet, but when I try to convert it to VBA, I get an error.

I figure I need to apply FormulaArray somewhere within following codes, any help is greatly appreciated.

I have tried with following codes but got #VALUE!

Public Function UDF_IndexMatch(Condition1, Condition2, COndition3)
    UDF_IndexMatch = Application.WorksheetFunction.Index(Range("$A$1:$J$7"), _
                                                  Application.WorksheetFunction.Match(Condition1, Range("$A$1:$A$7"), 0), _ 
                                                 Application.WorksheetFunction.Match(1, (Range("$A$1:$J$1") = Condition2) * (Range("$A$2:$J$2") = COndition3), 0))

End Function

the end result should be something like this:

= UDF_IndexMatch(Condition1, Condition2, COndition3) 

return: corresponding index match result

回答1:

I have misread your post at first and thought you would be better of with a standard formula, however you are specifically looking for a UDF. Just for the sake of giving other options, here is one:

Public Function ReturnVal(RNG As Range, Con1, Con2, Con3) As String
Dim SearchRng1 As String, SearchRng2 As String, SearchRng3 As String, SearchRng4 As String

With ActiveWorkbook.Sheets(RNG.Parent.Name)
    SearchRng1 = RNG.Parent.Name & "!" & RNG.Range(.Cells(3, 2), .Cells(RNG.Rows.Count, RNG.Columns.Count)).Address(False, False)
    SearchRng2 = RNG.Parent.Name & "!" & RNG.Range(.Cells(3, 1), .Cells(RNG.Rows.Count, 1)).Address(False, False)
    SearchRng3 = RNG.Parent.Name & "!" & RNG.Range(.Cells(1, 2), .Cells(1, RNG.Columns.Count)).Address(False, False)
    SearchRng4 = RNG.Parent.Name & "!" & RNG.Range(.Cells(2, 2), .Cells(2, RNG.Columns.Count)).Address(False, False)
End With
ReturnVal = Evaluate("=INDEX(" & SearchRng1 & ",MATCH(" & Con1 & "," & SearchRng2 & ",0),MATCH(""" & Con2 & """&""" & Con3 & """," & SearchRng3 & "&" & SearchRng4 & ",0))")

End Function

As long as you select the range in variables, it doesn't matter where your table is positioned or how big it is. You may also call it from another worksheet.

回答2:

You can just save the data in a variant array and iterate that:

Public Function UDF_IndexMatch(Condition1, Condition2, COndition3)
    Application.Volatile

    Dim rngarr As Variant
    rngarr = Application.Caller.Parent.Range("A1:J7").Value

    Dim irow As Long
    irow = 0

    Dim i As Long
    For i = LBound(rngarr, 1) To UBound(rngarr, 1)
        If rngarr(i, 1) = Condition1 Then
            irow = i
            Exit For
        End If
    Next i

    Dim jcolumn As Long
    jcolumn = 0

    If irow > 0 Then
        Dim j As Long
        For j = LBound(rngarr, 2) To UBound(rngarr, 2)
            If rngarr(1, j) = Condition2 And rngarr(2, j) = COndition3 Then
                jcolumn = j
            End If
        Next j
    End If

    If j > 0 Then

        UDF_IndexMatch = rngarr(irow, jcolumn)
    End If
End Function