vba 数组公式中的 MATCH 函数可以返回多个匹配项吗？

Can MATCH function in an array formula to return multiple matches?

提问by MgSam

I tried to use the MATCHfunction 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?

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

回答by Grade 'Eh' Bacon

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?

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

=SMALL(IFERROR(MATCH($B:$B,$A:$A,0),""),ROW()-ROW($C))

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.

这首先使用数组公式来显示搜索列中匹配的任何搜索词的每个“命中”，然后使用参考当前单元格行的 Small 函数，它返回最早的命中，然后是第二次命中，然后第三次打击等。

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.).

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

EDITOn 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:$A=$B,ROW($A:$A),""),ROW()-ROW($E))

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:

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

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

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

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

EDIT - Additional option

编辑 - 附加选项

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.

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

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)

第一个单元格看起来像这样，在单元格 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:$B,INDIRECT(ADDRESS(ROW(INDIRECT(H9))+1,1)):$A,0),""))+ROW(INDIRECT(H9)),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.

这将获取在上面一行中找到的单元格的地址（添加 1 行以避免重新命中相同的术语），并从该点到终点的新搜索列（添加 1 行以便它正确停止在最后一个结尾命中），它会重新搜索任何术语。

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.

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

回答by EEM

Working\developing on the formulas posted by @Grade'Eh'Bacon ended up with this formula to retrieve all the results of a match function with several matches for several items.

对@Grade'Eh'Bacon 发布的公式进行工作\开发，最终使用此公式检索匹配函数的所有结果，其中包含多个项目的多个匹配项。

Assuming input range is B2:B17and the range with the items to match is F3:F5enter this FormulaArrayin H3

假设输入范围B2:B17，并与项目相匹配的范围是F3:F5进入这个FormulaArray在H3

=IFERROR( SMALL( IF( $B:$B = TRANSPOSE( $F:$F ),
 1 + ROW( $B:$B ) - ROW( $B ), "" ), ROWS(:2  ) ), "" )

It's an FormulaArrayreturning all matches for several items

这是FormulaArray返回多个项目的所有匹配项

All merits go to @Grade'Eh'Bacon for his great work on the subject.

所有优点都归于@Grade'Eh'Bacon，因为他在该主题上的出色工作。

回答by MgSam

It is not possible with the built-in MATCH, however, using a VBA macro, you can achieve this:

内置 MATCH 是不可能的，但是，使用 VBA 宏，您可以实现：

Public Function MATCH_RANGE(values As Variant, ary As Variant, match_type As Integer)
    Dim i As Integer
    Dim elementCount As Integer
    Dim result()
    Dim value As Variant

    Dim arySize As Integer
    arySize = UBound(ary.Value2, 1)

    Dim valueSize As Integer
    valueSize = UBound(values.Value2, 1)

    ReDim result(0 To arySize, 0 To 1)

    elementCount = 0

    For i = 1 To arySize
        For j = 1 To valueSize
            value = values(j, 1)
            If (match_type = -1 And ary(i, 1) <= value) Or (match_type = 0 And ary(i, 1) = value) Or (match_type = 1 And ary(i, 1) >= value) Then
                result(elementCount, 0) = i
                elementCount = elementCount + 1
            End If
        Next j
    Next i

    For i = elementCount To arySize
        result(i, 0) = -100000000
    Next i

    MATCH_RANGE = result
End Function

This function both returns multiple matches and allows you to pass a range of multiple values that you want matched. I've found this useful a number of times. Feedback welcome to help improve this.

此函数既返回多个匹配项，又允许您传递要匹配的多个值的范围。我多次发现这很有用。欢迎反馈以帮助改善这一点。

NOTE: You must spread this formula across a few cells using an array-formula (CRTL-SHIFT-ENTER), in order to see the multiple matches.

注意：您必须使用数组公式 (CRTL-SHIFT-ENTER) 将此公式分布在几个单元格中，以便查看多个匹配项。