Excel跨表多值查找公式问题：仅返回单个结果需修正

• 支持Excel 365/2021动态数组的公式：

  =TEXTJOIN(", ", TRUE, XLOOKUP(FILTERXML("<m><n>"&SUBSTITUTE(C3,", ","</n><n>")&"</n></m>","//n"), 'Table 1'!A:A, 'Table 1'!B:B, ""))
  

• 兼容旧版Excel的公式：

  =TEXTJOIN(", ", TRUE, IFERROR(INDEX('Table 1'!B:B, MATCH(FILTERXML("<m><n>"&SUBSTITUTE(C3,", ","</n><n>")&"</n></m>","//n"), 'Table 1'!A:A, 0)), ""))
  

原公式问题说明 #

1. FILTERXML语法错误：XML结构不完整，无法正确拆分C列的多个姓名为独立数组元素；
2. MATCH未处理数组：MATCH默认仅返回第一个匹配项的位置，无法遍历拆分后的所有姓名逐一匹配城市。

公式逻辑解析 #

1. SUBSTITUTE(C3,", ","</n><n>")：将C列姓名间的逗号空格替换为XML标签，生成完整可解析的XML结构；
2. FILTERXML(..., "//n")：解析XML内容，提取所有姓名组成数组；
3. XLOOKUP/INDEX+MATCH：遍历数组中的每个姓名，从Table1中匹配对应的城市；
4. TEXTJOIN(", ", TRUE, ...)：将所有匹配到的城市用逗号空格连接，自动忽略空值。

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