Excel：没有通配符匹配的 MATCH() 公式

Question

我目前MATCH()在 Excel 2016 中使用的公式列例如：

= MATCH( [@[ITEM_CODE]], IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]], 0 )

INDEX()结果本质上是一个外键。它与或表达式一起用于其他列，OFFSET()以将当前行与另一个表中的匹配行关联起来。

某些查找值包含标点符号，包括特殊通配符*、?和~。这会导致意外匹配。一个真实的例子是商品代码，它无意中与另一个表中的*XA1商品代码匹配。1SC0021REXA1

针对上述示例，我目前的解决方案是：

= MATCH(
SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( [@[ITEM_CODE]], "~", "~~" ), "*", "~*" ), "?", "~?" ),
IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],
0
)

我不喜欢什么？写INDEX(MATCH())表达式时很容易忘记，容易出错（顺序很重要），而且很笨重，难以阅读。但我还没有找到不使用通配符的MATCH()替代方案，甚至没有找到简化替换表达式的方法，而不会在其他地方产生更多复杂性，例如在 VBA 中编写自定义函数。

Answer 1

您可以使用聚合：

=AGGREGATE(15,6,(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]])-MIN(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]))+1)/(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]=[@[ITEM_CODE]]),1)

或者您可以使用这个数组版本的 MATCH：

=MATCH(TRUE,[ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],0)

作为一个数组公式，退出编辑模式时需要用 Ctrl-Shift-Enter 进行确认。

这与第二个相同，但不需要 CSE：

=MATCH(TRUE,INDEX([ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],),0)