数组公式中的 MATCH 函数可以返回多个匹配项吗? [英] Can MATCH function in an array formula to return multiple matches?

问题描述

我尝试在数组公式中使用 MATCH 函数返回多个匹配项(默认情况下它只返回第一个匹配项).但是，这似乎不起作用.如果没有复杂的、不可读的公式，我该如何解决这个问题?

I tried to use the MATCH function in an array formula to return multiple matches (by default it only returns the first match). However, this doesn't seem to work. How can I solve this problem without a complex, unreadable formula?

推荐答案

这个怎么样，没有 VBA?[使用 CTRL + SHIFT + ENTER 作为数组公式在单元格 C9 上输入，其中您搜索的列是 A9:A24，您的搜索词在 B1:B4]，然后向下拖动以查找多个匹配项?

How about this, without VBA? [entered on cell C9 as an array formula with CTRL + SHIFT + ENTER, where your searched column is A9:A24, and your search terms are in B1:B4], and dragged down to find multiple hits?

=SMALL(IFERROR(MATCH($B$1:$B$4,$A$9:$A$24,0),""),ROW()-ROW($C$8))

这首先使用数组公式来显示搜索列中匹配的任何搜索词的每个命中"，然后使用参考当前单元格行的 Small 函数，它返回最早的命中，然后是第二个命中，然后是第 3 次命中，依此类推

This first uses the array formula to show each 'hit' for any of the search terms matched in the searched column, and then using the Small function with reference to the current cell's row, it returns the earliest hit, then the 2nd hit, then the 3rd hit, etc.

除此之外，可以根据需要使用搜索数组的引用点(转换为索引函数的行位置等).

Beyond this point, the reference points to the searched array can be used as needed (converted to the row location of an index function, etc.).

编辑进一步查看此公式的结果后，它只会为每个搜索词返回一次命中，即使该搜索词出现多次也是如此.为了解决这个问题，我首先使用了以下公式:

EDIT On further review of the results from this formula, it only returns a single hit for each search term, even if that search term appears multiple times. To resolve this, I first used the formula:

=SMALL(IF($A$9:$A$24=$B$1,ROW($A$9:$A$24),""),ROW()-ROW($E$8))

这显示了在 B1 中找到的搜索词匹配的每次命中.这是我被困的地方.我只能弄清楚如何使用公认的手册来解决:

This shows each hit for a match of the search term found in B1. Here is where I am stuck. I could only figure out how to resolve with the admittedly manual:

=SMALL(IF($A$9:$A$24={"a","b","c"},ROW($A$9:$A$24),""),ROW()-ROW($E$8))

关于如何改进以允许多个术语多次点击的任何建议?

Any suggestions on how to improve to allow multiple hits for multiple terms?

编辑 - 附加选项

好的，我已经确定了另一种获取多次点击的方法.这个依赖于考虑之前已经进行的匹配的位置.根据您希望结果向量的样子(OP 从未指定过)，由此得出的结果是干净的，但公式相当混乱.

Okay, I've determined another method of picking up multiple hits. This one relies on considering the location of the previous matches already made. Depending on what you want your result vector to look like (which was never specified by the OP), the results from this are clean but the formula is fairly messy.

第一个单元格看起来像这样，在单元格 H9 中:=ADDRESS(MIN(IFERROR(MATCH($B$1:$B$4,$A$9:$A$24,0),""))+ROW($A$8),1)

The first cell looks like this, in cell H9: =ADDRESS(MIN(IFERROR(MATCH($B$1:$B$4,$A$9:$A$24,0),""))+ROW($A$8),1)

这将使用上面提到的公式显示与任何搜索词匹配的第一个单元格的地址.

This shows the address of the first cell which matches any of the search terms, using the formula noted further above.

下面的单元格(以及之后的每个单元格)都有这个(也是一个数组公式):

The cell below that (and every cell after that), has this (also an array formula):

=ADDRESS(MIN(IFERROR(MATCH($B$1:$B$4,INDIRECT(ADDRESS(ROW(INDIRECT(H9))+1,1)):$A$25,0),""))+ROW(INDIRECT(H9)),1)

这会选取在上一行中找到的单元格的地址(添加 1 行以避免再次命中相同的术语)，并从该点到终点的新搜索列(添加 1 行，以便它在最后一个结束命中处正确停止)，它会重新搜索任何术语.

This picks up the address of the cell found in the row above (adding 1 row to avoid re-hitting the same term), and from that new search column from that point to the end point (adding 1 row so that it properly stops at the last ending hit), it re-searches for any of the terms.

这又是一个，不是那么干净 [是的，我知道我可以做一些改进来确定搜索应该是什么 - 要么使用文本操作功能，要么甚至做一个随着您向下移动而改变的相对名称引用column]，但它是自动化的，我认为比 VBA 模块更干净.尤其是，根据您想要的结果向量，这可能会简单得多.

This one is again, not that clean [Yes I know there are some improvements I could make to determining what the search should be - either using the text manipulation functions or even doing a relative name reference that changes as you move down the column], but it is automated and, I would argue, cleaner than a VBA module. Especially as, depending on what you want your result vector to be, this could be much simpler.

这篇关于数组公式中的 MATCH 函数可以返回多个匹配项吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！