如何在分类下某条目与输入值部分匹配时返回对应分类？

工作表结构说明 #

• 输入表：专门存放需要匹配分类的条目名称（比如将输入值放在A列）
• 分类表：按分类归集匹配词汇——第一行是分类名称（例如A1填「Drink」、B1填「Food」），分类名下方的单元格存放该分类对应的匹配词（例如A2填「Tea」、A3填「Coffee」；B2填「Burger」、B3填「Fries」）

具体实现公式 #

适用于Excel 365/2021（动态数组版本） #

在输入表的B2单元格（对应A2的输入值）粘贴以下公式，下拉即可批量应用：

=TEXTJOIN(", ",TRUE,INDEX(分类表!$1:$1,,MATCH(TRUE,ISNUMBER(SEARCH(分类表!$2:$100,A2)),0)))

• 逻辑说明：
  1. 用SEARCH检查分类表所有匹配词是否出现在输入条目中
  2. 将结果转为TRUE/FALSE标记出有匹配的位置
  3. 定位第一个匹配对应的分类列，提取分类名称
  4. 若存在多个分类匹配，自动用逗号分隔显示结果

兼容旧版Excel（数组公式） #

如果使用旧版Excel，无动态数组功能，输入以下公式后需按Ctrl+Shift+Enter触发数组计算：

=INDEX(分类表!$1:$1,MAX(IF(ISNUMBER(SEARCH(分类表!$2:$100,A2)),COLUMN(分类表!$2:$100),0)))

• 逻辑说明：找到所有匹配的分类列，返回最后一个匹配的分类；若要返回第一个匹配分类，将MAX替换为MIN即可

支持分类增删的设置 #

要实现新增/删除分类或匹配词后自动生效，推荐把分类表的每个分类区域转成结构化表格：

1. 选中某分类的所有内容（包含分类标题）
2. 按Ctrl+T，勾选「表包含标题」，确认创建
3. 后续直接在表格末尾新增行、删除行，表格范围会自动调整，公式也能自动识别新范围

比如Drink分类的表格命名为Table_Drink，Food分类的表格命名为Table_Food，公式可以改成更直观的版本：

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(Table_Drink[Drink],A2)),"Drink",""),IF(ISNUMBER(SEARCH(Table_Food[Food],A2)),"Food",""))

后续新增分类时，只需添加对应的结构化表格，并在公式中新增一行IF判断即可。

示例效果 #

• 输入「Bubble Tea」，分类表Drink列存在「Tea」，返回「Drink」
• 输入「Tea Burger」，同时匹配Drink和Food分类，返回「Drink, Food」

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