从列表中查找单元格中最长的字符串匹配并返回匹配字符串的公式

Question 1

你说你想找到最长的字符串匹配。这个术语有点含糊。我将其解释为“匹配（完整）的最长字符串”。

在我看来，找到匹配的最长字符串的第一步是找到这的长度匹配的最长字符串。

我假设你要搜索的字符串为了在列 L（即“列表”）中，并且要搜索的字符串在位于第列 A，从第 1 行开始，两列均如此。（见下面的示例。）我们可以通过输入来获取匹配的最长字符串的长度

=MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))

作为辅助列（例如， Column B）。此公式和此答案中的所有其他公式都是数组公式；输入后必须按Ctrl+ Shift+ （又名 CSE），因此它们将出现在公式栏中，并用括号（和）括起来。在我的示例数据（如下）中，对于第 1 行（其中包含“Once upon a midnight dreary,”），这将创建一个虚拟数组，看起来像 {0,0,6,0,0,0,0,0,0,0,5,0,…}，因为包含“dreary”（）和“night”（），其长度分别为 6 和 5。然后从该数组中取最大数字，得到 6（“dreary”的长度，即最长的匹配字符串）。Enter{}A1A1L3L11

然后我们使用与您尝试过的技术类似的技术：

=INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0))

进入C1，按 CSE，然后向下拖动/填充。这将重新创建相同的虚拟数组 ( {0,0,6,0,0,0,0,0,0,0,5,0,…} )，在其中搜索 6（的值 B1），并在第三个成员中找到它。然后它使用 3 作为关键字列表中的索引并得到“dreary”，这确实是“Once upon a midnight dreary”中出现的六个字母的单词。

B1我们可以通过在上面显示的公式中插入公式来消除辅助列C1：

=INDEX(L$1:L$99,MATCH(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0)),IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0))

这就是我的示例列中的内容 D。

第 4 行与您在问题中引用的案例完全类似： A4是“被遗忘的传说之卷”；它包含“被遗忘的”（L4）和“被遗忘的”（L5）。公式找到“被遗忘的”（较长的）。

请注意，关键字列表的顺序仍然很重要（就像您尝试的那样）。 A5 （“while I nodded, near napping,”）包含“nodded”（L12）和“nearly”（L9）。它们都是 6 个字符长，所以B5也是 6，所以C5显示 D5“nearly”，因为它出现在关键字列表（列 L）中“nodded”之前。

如果您的“搜索”字符串始终包含至少一个“搜索”字符串，那么这应该足够好了。但请注意单元格 A6（“突然传来一阵敲击声”），它不包含任何关键字。这会导致B6 = 0，并INDEX愚蠢地将其视为 1 并报告“curious”，这是关键字列表中的第一个单词。我们通过以下方法防止这种情况发生

=IF($B1=0, "", INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))

在 Column 中 E，它明确处理 Column 中为零的情况 B。如果您希望显示除空白字段之外的其他内容（例如错误消息），请将其放在引号内（在之后$B1=0,）。

总结

如果你需要在一列中完成所有操作（没有辅助列），请使用

=IF(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))=0, "", INDEX(L$1:L$99,MATCH(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0)),IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))

（带++ Ctrl）。这是上面的列公式，其中嵌入了公式（两次），并在插图中以列显示。如果您愿意使用辅助列，请将其定义为ShiftEnterEB1F

=MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))

并得到结果

=IF($B1=0, "", INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))

B1如果有必要，用辅助列替换，并对两者都使用 CSE。

PS 点击编辑以文本形式获取我的测试数据。

Answer