Excel中如何匹配单元格内值并返回所有匹配结果

前置准备 #

• 先构建一个分类映射表（比如放在Sheet2），记录每个项目对应的分类：

  项目	分类
  Apple	水果
  pear	水果
  trousers	服饰
  computer	科技

适用于Excel 365/2021及以上版本的公式 #

利用TEXTJOIN+FILTER+TEXTSPLIT组合实现自动匹配并拼接所有结果，无需数组输入：

在B2单元格（对应「服饰」列）输入以下公式，然后向右拖动填充至其他分类列：

=TEXTJOIN("; ", TRUE, FILTER(TEXTSPLIT(A2, "; "), XLOOKUP(TEXTSPLIT(A2, "; "), Sheet2!A:A, Sheet2!B:B, "")=B$1))

公式说明 #

• TEXTSPLIT(A2, "; ")：将A列的多项目字符串按「分号+空格」拆分为单个项目的数组
• XLOOKUP(...)：为每个拆分后的项目匹配对应的分类，无匹配时返回空值
• FILTER(...)：筛选出分类等于当前列标题（如B$1为「服饰」）的项目
• TEXTJOIN("; ", TRUE, ...)：将筛选结果用「分号+空格」拼接，自动忽略空值

旧版Excel兼容方案（无TEXTSPLIT/FILTER） #

如果使用旧版Excel，需输入数组公式（输入后按Ctrl+Shift+Enter确认）：

在B2单元格输入：

=TEXTJOIN("; ", TRUE, IF(XLOOKUP(TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",99)),(ROW($1:$100)-1)*99+1,99)),Sheet2!A:A,Sheet2!B:B,"")=B$1,TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",99)),(ROW($1:$100)-1)*99+1,99)),""))

注：ROW($1:$100)假设单单元格最多包含100个项目，可根据实际情况调整数字。

关键注意点 #

• 匹配时大小写敏感，可通过LOWER()/UPPER()统一大小写，例如将TEXTSPLIT(A2, "; ")改为LOWER(TEXTSPLIT(A2, "; "))，同时将映射表的项目也改为小写
• 确保A列的项目分隔符与公式中的一致，若仅用分号分隔，将公式中的"; "替换为";"

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