vba excel 匹配多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15165714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba excel match on multiple criteria
提问by mango
any help would be appreciated. this looks like a common question, but I couldn't find any in vba. Here's my best shot:
任何帮助，将不胜感激。这看起来像是一个常见问题，但我在 vba 中找不到任何问题。这是我最好的镜头：
Dim oppositeRow As Long
.....
oppositeRow = WorksheetFunction.Match(ddate & campaign, "A3:B62", 0)
回答by Alex Godofsky
If you know that there will only be one matching result, you can do the following as an Excel formula:
如果您知道只会有一个匹配结果，您可以使用 Excel 公式执行以下操作：
=SUMPRODUCT(ROW(A2:A5), N("john"=A2:A5), N("smith"=B2:B5))
as applied to the following worksheet:
适用于以下工作表：
A B 1 first last 2 bob smith 3 john smith 4 john brown 5 sam brown
This will return 3 (since john smith is on the third row), you will have to subtract 1 to get the index into the table because of the header row.
这将返回 3（因为 john smith 在第三行），由于标题行，您必须减去 1 才能将索引放入表中。
This works by multiplying together three vectors:
这是通过将三个向量相乘来实现的：
- The first, ROW(A2:A5), is just {2,3,4,5}.
- The second, N("john"=A2:A5), is {0,1,1,0}. The N() takes the vector {false,true,true,false} and converts it to numbers.
- The third, N("smith"=B2:B5), is {1,1,0,0}. Same deal with the N().
- 第一个，ROW(A2:A5)，就是 {2,3,4,5}。
- 第二个 N("john"=A2:A5) 是 {0,1,1,0}。N() 获取向量 {false,true,true,false} 并将其转换为数字。
- 第三个 N("smith"=B2:B5) 是 {1,1,0,0}。与 N() 相同。
Multiplying the vectors together element-wise gets {0,3,0,0}, the sum of which is 3.
将向量按元素相乘得到 {0,3,0,0}，其总和为 3。
It is somewhat awkward to express a complicated formula like that in VBA, so if you want to do it purely in VBA I'd recommend just doing a loop with a counter and recording the index where you found a row matching your criteria.
在 VBA 中表达这样一个复杂的公式有点尴尬，所以如果你想纯粹在 VBA 中做到这一点，我建议你只用一个计数器做一个循环，并记录你找到符合条件的行的索引。
Edit: here is some VBA code:
编辑：这是一些 VBA 代码：
Dim index as Integer
For index = 1 to 4
If Range("A1").Offset(index).Value = "john" And Range("B1").Offset(index).Value = "smith" Then
Exit For
End If
Next index
回答by Stewbob
You can accomplish this with the DGETformula, as long as you add a column to your worksheet that is used to represent the row number.
您可以使用DGET公式完成此操作，只要向工作表中添加一列用于表示行号即可。
rowID column1 column2 1 A aa 2 B bb 3 C cc 4 D dd 5 E ee
The formula is then:
那么公式为：
=DGET(A1:C6;1;E1:F2)
This assumes that the above data is in A1:C6The below block of data, which includes the search criteria is placed in E1:F2
这假设上面的数据在A1:C6下面的数据块中，其中包括搜索条件放在E1:F2
column1 column2 C cc
The return value of the DGETformula listed above is '3'.
DGET上面列出的公式的返回值为“3”。
You can do this purely in VBA if your VBA method places the proper search criteria into cells E2and F2prior to running the DGETformula.
如果您的 VBA 方法在运行公式之前将正确的搜索条件放入单元格中E2，则您可以完全在 VBA 中执行此操作。F2DGET
