如何使用数组公式（按 CTR+SHIFT+ENTER 键）查找两个匹配函数的交集。
目前我尝试使用这个数组公式但无法得到任何结果。
{=MATCH(B2,Sheet2!$A$2:$A$5) MATCH(C2,Sheet2!$B$2:$B$5)}
例如，Sheet2 中的数据如下所示。
A B C
1 Code Type Value
2 ID1 DIRECT 100
3 ID1 INDIRECT 130
4 ID2 DIRECT 200
5 ID2 INDIRECT 110
我在 Sheet1 中的数据如下所示。
A B C
1 Date Code Type
2 3/1/2013 ID2 INDIRECT
通过使用上述数组公式，我希望得到两个数组（行号）的交集：
= {4,5} {3,5}
= {5}
答案1
尝试这个公式（以数组公式输入）：
=MATCH(B2&C2,Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5,0)
答案2
我可以得到一个包含匹配行号和不匹配虚拟值的数组。使用数组条目：
=IF($A$2:$A$5=D2,IF($B$2:$B$5=E2,ROW($A$2:$A$5),2000000),2000000)
我还可以获取单个单元格中的匹配项，但无法获取一个单元格中的数组中的匹配项。使用数组条目（在第 2 行）并向下填充，直到达到虚拟值：
=SMALL(IF($A$2:$A$5=D2,IF($B$2:$B$5=E2,ROW($A$2:$A$5),2000000),2000000),ROW()-1)
要将数组折叠为匹配的大小，我相信您需要一个（相当简单的）VBA 函数。
答案3
如果您只希望有一个匹配项，那么 Peter 的建议应该可以满足您的要求。如果不是，那么您可以扩展 Excellll 的解决方案以获取仅包含匹配行号的数组，例如，如果第 2 行和第 5 行在两列上都匹配，则此公式将返回 {2;5}
=SMALL(IF(($A$2:$A$5=D2)*($B$2:$B$5=E2),ROW($A$2:$A$5)),ROW(INDIRECT("1:"&SUM(($A$2:$A$5=D2)*($B$2:$B$5=E2)))))
确认CTRL+SHIFT+ENTER
注意：如果这是获得不同结果的中间步骤，那么这可能不是最好的方法......
答案4
在 Excel 365 中，我们可以使用数组构造。 Spill 行为意味着我们不必输入 Ctrl-Shift-Enter。首先，设置两个单独的垂直数组。在此示例中，一个从单元格 F2 开始，第二个从 M2 开始。在第三列中，输入此 LET 函数。它将生成一个新的垂直数组，由两个输入数组的交集组成：
=LET(a, F2#, b, M2#, u, UNIQUE(VSTACK(a, b)), ua, MATCH(a, u, 0), ub, MATCH(b, u, 0), rs, IF(ua*ub, u, ""), SORT(rs))