Excel：如何从文本字符串中匹配提取列表中的产品编号

场景回顾 #

K列存储评论内容，需从Products工作表B列的产品编号列表中，提取评论里包含的编号。单个匹配的IF嵌套公式过于繁琐，此前尝试的LOOKUP、AGGREGATE公式返回0，以下是可行解决方案：

方法1：Excel 365/2021 动态数组解法（支持多匹配） #

适合支持动态数组的新版本Excel，可同时提取评论中所有匹配的产品编号，用逗号分隔：

=TEXTJOIN(", ", TRUE, FILTER(Products!$B$2:$B$500, ISNUMBER(SEARCH(Products!$B$2:$B$500, K5)), "Nope"))

• 逻辑：FILTER筛选出评论中包含的所有产品编号，TEXTJOIN将结果合并为字符串，无匹配时返回"Nope"。

方法2：修正LOOKUP公式（兼容旧版本Excel） #

此前公式返回0的核心原因是未处理SEARCH返回的错误值，修正后可提取最后一个匹配的产品编号：

=IFERROR(LOOKUP(2^15, 1/ISNUMBER(SEARCH(Products!$B$2:$B$500, K5)), Products!$B$2:$B$500), "Nope")

• 逻辑：1/ISNUMBER(...)将错误值转为#DIV/0!，LOOKUP会自动忽略错误值，找到最后一个符合条件的产品编号；IFERROR处理无匹配的情况，返回"Nope"。

方法3：修正AGGREGATE公式（兼容旧版本Excel） #

此方法可提取第一个匹配的产品编号，需确保产品列表无空单元格：

=IFERROR(INDEX(Products!$B$2:$B$500, AGGREGATE(15, 7, (ROW(Products!$B$2:$B$500)-ROW(Products!$B$2)+1)/ISNUMBER(SEARCH(Products!$B$2:$B$500, K5)), 1)), "Nope")

• 逻辑：(ROW(...) - ROW(Products!$B$2)+1)计算产品列表的相对行号，AGGREGATE(15,7,...)忽略错误值并取最小的匹配行号（即第一个匹配项），INDEX提取对应编号；无匹配时返回"Nope"。

关键注意事项 #

• 产品列表范围Products!$B$2:$B$500请勿包含空单元格，空单元格会导致SEARCH匹配所有评论，返回错误结果。
• 若产品编号存在包含关系（如C21和C210），可给编号加边界符避免误匹配，修改SEARCH部分为：

  SEARCH(" "&Products!$B$2:$B$500&" ", " "&K5&" ")
  

内容的提问来源于stack exchange，提问作者KACB