可以通过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?

（注意：我会自己发布这个问题的答案，发现问题正确，也不是一个很好的答案。StackOverflow鼓励这样的知识共享，所以请跳过火焰。

推荐答案

p>怎么样，没有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函数参考当前单元格的行，它返回最早的命中，然后是第二个命中，然后是第三个命中等。

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.

这一个再一次，不是那么干净[是的，我知道有一些改进，我可以做出确定什么搜索应该是 - 使用文本操作函数，甚至做一个相对名称引用，改变当你向下移动列]，但它是自动化的，我认为比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屋！