如何为多变量索引匹配公式创建UDF [英] How to create UDF for multi-variable Index Match formula

问题描述

我想为以下Excel索引匹配公式创建UDF:

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))}

在此处输入图片描述

    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))}                         

注意:CTRL + SHIFT + ENTER返回Excel中的数组结果

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

这在excel工作表中有效，但是当我尝试将其转换为VBA时，出现错误.

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

我认为我需要在以下代码内的某个地方应用FormulaArray，非常感谢您的帮助.

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

我尝试使用以下代码，但获得了#VALUE！

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

推荐答案

我最初看错了您的帖子，并认为使用标准公式会更好，但是您正在寻找UDF.只是为了给出其他选择，这里是一个:

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.

这篇关于如何为多变量索引匹配公式创建UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！