Excel for Mac 16/2021：多关键词匹配对应ID并批量返回的无VBA解法

正确公式（优先推荐） #

如果你的Excel版本支持TEXTSPLIT（Excel for Mac 2021/16.52及以上版本支持），使用以下公式：

=TEXTJOIN(", ", TRUE, XLOOKUP(TEXTSPLIT(D2, ", "), NPC_Names, NPC_IDs, "NPC invalid", 0))

如果TEXTSPLIT不可用，用兼容的FILTERXML替代方案：

=TEXTJOIN(", ", TRUE, XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(D2, ", ", "</s><s>")&"</s></t>", "//s"), NPC_Names, NPC_IDs, "NPC invalid", 0))

公式说明 #

• TEXTSPLIT(D2, ", ") / FILTERXML(...)：将D2单元格中逗号+空格分隔的NPC名称拆分为独立的名称数组（比如把"Candi, Giles"拆成{"Candi", "Giles"}）。
• XLOOKUP(...)：对拆分后的每个名称，在NPC_Names命名范围中精准匹配，返回对应的NPC_IDs值；匹配失败时返回"NPC invalid"。
• TEXTJOIN(", ", TRUE, ...)：将所有匹配到的ID用逗号+空格连接成最终字符串，忽略空值。

你之前的问题分析 #

1. TEXTJOIN+XLOOKUP失败：你直接用XLOOKUP($D2, ...)，这里$D2是整个逗号分隔的字符串，而非拆分后的单个名称，XLOOKUP找不到匹配项，所以返回"NPC invalid"。
2. TEXTJOIN+FILTER失败：你的ISNUMBER(SEARCH($D2, NPC_Names))逻辑颠倒了——它是检查NPC_Names中的条目是否包含整个D2文本，而非D2中的每个名称是否存在于NPC_Names中，所以没有符合条件的结果，返回空白。

注意事项 #

• 确保NPC_Names和NPC_IDs两个命名范围长度一致，且分别对应NPC_Codes工作表中的名称列和ID列。
• 如果你的NPC名称分隔符只有逗号（无空格），把公式中的", "替换为","即可。

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