在列表中查找并返回单元格内第一个匹配项

假如您有一个关键词列表，想要从某个包含多个值的单元格中获取首次出现的关键词，您可以结合使用 INDEX、MATCH、AGGREGATE 和 SEARCH 等函数公式来轻松实现。

如何在单元格中根据列表查找第一个匹配值？

若要获取单元格内与关键词列表匹配的首个关键词（如上表所示），需执行“包含匹配”而非精确匹配。为此，可利用 SEARCH 函数，将单元格中每个关键词出现的位置（以数字形式）传递给 AGGREGATE 函数，然后通过设置 function_num 参数为 15，ref 2 参数为 1，让 AGGREGATE 获取最小值。接下来，使用 MATCH 定位首个最小值，并将其位置编号传递给 INDEX，返回对应的关键词。

通用语法

=INDEX(）keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(）））keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ 注意：这是一个数组公式，需要同时按下 Ctrl+Shift+Enter 键盘输入。

要获取 B5 单元格与关键词列匹配的首个关键词，请在 C5 单元格中复制或输入以下公式，并按 Ctrl+Shift+Enter，即可得到结果：

=INDEX(）$E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH(）））$E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ 注意：上文中的美元符号（$）表示绝对引用，意味着 keyword_rng 在公式中引用不会因移动或复制到其他单元格而变化。而未加美元符号的 lookup_cell 因需动态变化。输入公式后，下拉填充柄以应用到下方单元格。

公式详解

=INDEX($E$5:$E$7,）MATCH(）AGGREGATE(15,6,）SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5)：SEARCH 函数会返回在数字中找到范围 $E$5:$E$7 内每个关键词的位置，若未找到，则返回 #VALUE!错误。结果将以如下数组形式呈现：{15;11;#VALUE!}。
• AGGREGATE(15,6,）SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,）{15;11;#VALUE!},1)： 当 AGGREGATE 函数配合 function_num 参数为 15 和 option 参数为 6 使用时，可根据 ref 2 参数 1，自动忽略错误值，返回数组中的最小值。因此，该公式最终返回 11.
• MATCH(）AGGREGATE(15,6,）SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH(）11,{15;11;#VALUE!},0)：match_type 参数 0 会让 MATCH 函数进行精确匹配，并返回 11 在数组 {15;11;#VALUE!} 中的位置。因此，公式将返回 2.
• INDEX($E$5:$E$7,）MATCH(）AGGREGATE(15,6,）SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2）：INDEX 函数随后会返回 $E$5:$E$7 范围中第 2 个位置的值，即 bbb。

备注

• 如果单元格中未包含任何关键词，则会返回 #NUM!错误。
• 该公式不区分大小写。如需进行区分大小写的匹配，只需将 SEARCH 函数替换为 FIND 即可。

相关函数

相关公式