vba 具有多个条件的索引/匹配函数查找数组

Index/Match Function Lookup Array with multiple criteria

提问by D_Zab

I looked but could not find a valid response for this specific circumstance.

我查看了但找不到针对此特定情况的有效响应。

I need to write a macro that searches a table for a specific Name (Column A), Material (Column B), and Color (Column C) combination then returns the price from Column D.

我需要编写一个宏，在表格中搜索特定名称（A 列）、材料（B 列）和颜色（C 列）组合，然后从 D 列返回价格。

I can enter in the formula (array)

我可以输入公式（数组）

=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!G1:G5552=C1),0),4)

Where A1 has the Name of the item I am searching for, B1 has the Material and C1 has the Color and the value returns just fine. However, I have set it up to quickly choose between Name/Material/Color and I want to be able to pull the prices quickly so I am trying to write a Macro to run when I click a button.

其中 A1 具有我要搜索的项目的名称，B1 具有材料，C1 具有颜色，并且该值返回得很好。但是，我已将其设置为在名称/材料/颜色之间快速选择，并且我希望能够快速拉动价格，因此我尝试编写一个宏以在单击按钮时运行。

The problem I am encountering is transposing the MATCH(1,(Sht1!A1:A5552=A1)(Sht1!B1:B5552=B1)(Sht1!C1:C5552=C1),0) part into VBA. I am trying to avoid just using the record function because it spits a .ForumlaArray out in R1C1 format that is difficult to interpret and update in the future.

我遇到的问题是将 MATCH(1,(Sht1!A1:A5552=A1) (Sht1!B1:B5552=B1)(Sht1!C1:C5552=C1),0) 部分转换为 VBA。我试图避免只使用 record 函数，因为它会以 R1C1 格式输出一个 .ForumlaArray，将来很难解释和更新。

I have tried

我试过了

Application.Index(Sht1.Range("A1:D5552"), _
  Application.Match(1, Sht1.Range("A1:A5552") = Range("A1") & _
  "*" & Sht1.Range("B1:B5552") = Range("B1") & "*" & Sht1.Range("C1:C5552") = _
  Range("C1"), 0), 4)

But get a Type Mismatch

但得到一个类型不匹配

I also tried putting in the .ForumlaArray but get an "unable to set FormulaArray property of the range class" error (because it needs R1C1 Format)

我也尝试放入 .ForumlaArray 但得到“无法设置范围类的 FormulaArray 属性”错误（因为它需要 R1C1 格式）

"=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!C1:C5552=C1),0),4)"

I have variables set up a little differently but I am trying to simplify these examples to make it easier to interpret.

我的变量设置略有不同，但我试图简化这些示例以使其更易于解释。

回答by Rick supports Monica

I think you would be better off with a completely different approach. Instead of having VBA doing the lookup, I would use VBA to maintain lists of unique values in your table columns, each contained in a named range. Then use Data Validation to create a drop down menu for each search item (Name, Material, Color) populated by the VBA created named range, and continue to use the array formula you presented above to perform the actual lookup.

我认为采用完全不同的方法会更好。我将使用 VBA 来维护表列中唯一值的列表，而不是让 VBA 进行查找，每个值都包含在一个命名范围中。然后使用数据验证为由 VBA 创建的命名范围填充的每个搜索项（名称、材料、颜色）创建一个下拉菜单，并继续使用您上面提供的数组公式来执行实际查找。

See Jean-Fran?ois Corbett's answer for how to Populate unique values in to VBA array from excel.

请参阅 Jean-Fran?ois Corbett 的回答，了解如何从 excel将唯一值填充到 VBA 数组中。

Using a Tablecombined with structured references, you can add more entries to the bottom of your Tableand it will auto-magically expand to include them. Set your VBA code to build the new list of unique values (and store them in named ranges, e.g. NamesList, MaterialsList, ColorList) when the Tablechanges.

使用Table结合结构化引用，您可以在底部添加更多条目Table，它会自动神奇地扩展以包含它们。设置您的 VBA 代码以在更改时构建新的唯一值列表（并将它们存储在命名范围内，例如NamesList, MaterialsList, ColorList）Table。

Another option would be to simply use a Pivot Table.

另一种选择是简单地使用数据透视表。

EDIT: Here's another nice approachto building a list of unique values from a Range.

编辑：这是从Range.

回答by cheezsteak

You are incorrectly using the Application.Matchfunction and some fundamentals of VBA. In short, the function does not work like the Formula does.

您错误地使用了Application.MatchVBA的函数和一些基础知识。简而言之，该函数不像公式那样工作。

AFAIK Application.Matchtakes a value to look for, and a 1D range of cells to look for it and returns the index of the cell in the array. So it sounds like you will need to do it multiple times to for each column. Granted, I can't find any acceptable documentationon it.

AFAIKApplication.Match需要一个要查找的值和一维单元格范围来查找它并返回数组中单元格的索引。所以听起来你需要为每一列做多次。当然，我找不到任何可接受的文档。

A correct usage would be:

正确的用法是：

row_index = Application.Match(Sht1.Range("A1").Value, Sht1.Range("A1:A5552"), 0)
price_value = Sht1.Cells(row_index, "D").Value

But that will only return the first match, not all of them. VBA won't increase performance if all you are doing is using replicating the formula in VBA. Other posters are correct that a different approach might be necessary.

但这只会返回第一场比赛，而不是全部。如果您所做的只是在 VBA 中使用复制公式，则 VBA 不会提高性能。其他海报是正确的，可能需要采用不同的方法。

---

---

Some other fundamentals you are getting wrong mostly stem from you taking the formula string and trying to translate it directly into Application.Matchwhich AFAIK you can't do.

您出错的其他一些基础知识主要源于您使用公式字符串并尝试将其直接转换为Application.Match您无法执行的 AFAIK。

You are trying to specify conditions of when you have a match like this:

您正在尝试指定这样的匹配条件：

Sht1.Range("A1:A5552") = Range("A1")

What you are really doing is trying to compare Ranges. VBA is trying to evaluate this as a boolean before passing it to Application.Match. This returns a Type Error because the ranges are of different dimensions.

您真正在做的是尝试比较范围。VBA 试图在将其传递给Application.Match. 这将返回类型错误，因为范围具有不同的维度。

In case you didn't already do this: Sht1needs to be declared as a Worksheetobject and set to ActiveWorkbook.Sheets("Sht1")

如果您还没有这样做：Sht1需要声明为Worksheet对象并设置为ActiveWorkbook.Sheets("Sht1")

Dim Sht1 As WorkSheet
Set Sht1 = ActiveWorkbook.Sheets("Sht1")

you can't simply hope that the variable Sht1already refers to the worksheet "Sht1".

您不能简单地希望变量Sht1已经引用了工作表“Sht1”。

Then you try to string these together like this.

然后你试着像这样把它们串在一起。

Sht1.Range("A1:A5552") = Range("A1") & "*" & _
Sht1.Range("B1:B5552") = Range("B1") & "*" & _
Sht1.Range("C1:C5552") = Range("C1")

I think you understand that &is for string concatenation but you are trying to concatenate, the strings "*"with the booleans/ranges which is undefined.

我想您明白这&是用于字符串连接，但您正在尝试连接"*"带有未定义布尔值/范围的字符串。

A VBA Solution

VBA 解决方案

If you want a solution using VBA, you can either walk through all rows until you find a match:

如果您想要使用 VBA 的解决方案，您可以遍历所有行，直到找到匹配项：

Function FindProduct(name As String, material As String, color As String) As String

    Dim product_sheet As WorkSheet
    Set product_sheet = GetProductSheet() ' Workbooks(BOOK_NAME).Sheets(SHEET_NAME)

    Dim row_index as Long
    For row_index = 2 To product_sheet.UsedRange.Rows.Count

        If product_sheet.Cells(row_index, NAME_COL).Value = name _
        And product_sheet.Cells(row_index, MATERIAL_COL).Value = material _
        And product_sheet.Cells(row_index, COLOR_COL).Value = color Then

            FindProduct = product_sheet.Cells(row_index, PRODUCT_COL).Value
            Exit Function

        End If

    Next row_index

End Function

Or if performance is an issue:

或者，如果性能是一个问题：

• Keep your data sorted on columns "A", "B", "C" (for this example, in that order.)
• Find the range of rows that match in column "A"
• From that range, find the sub range of rows that match in column "B"
• From that range, return the sub range of rows that match in column "C"

• 将数据按“A”、“B”、“C”列排序（在本例中，按此顺序排列。）
• 查找“A”列中匹配的行范围
• 从该范围中，找到“B”列中匹配的行的子范围
• 从该范围中，返回与列“C”中匹配的行的子范围

I may provide that later.

我可以稍后提供。