我正在尝试创建一个公式，能够自动把源单元格中的所有关键字与它们相应的ID编码逐一匹配，然后在另一个单元格中返回所有匹配的ID编码。如何在Excel for Mac 16/2021版本中实现这一点，而不需要回退到VBA？

这张电子表格是一份我正在运行的桌面RPG的时间线。

Timeline工作表有一列用于NPC。NPC列中的单元格包含多个名称，名称之间用逗号分隔。

一个NPC名称及其对应ID代码的主清单位于名为“NPC_Codes”的工作表中。

我希望将一个NPC单元格中列出的所有NPC的代码显示在相邻的单元格中（例如，“Candi, Giles”返回“NPC-001, NPC-005”）。

我用XLOOKUP来匹配单个关键字并返回其ID代码。

=XLOOKUP($D2,NPC_Names,NPC_IDs,"NPC invalid",0,-1)&""

随后我尝试使用TEXTJOIN和 XLOOPUP来实现多次匹配并返回结果。结果返回了“NPC invalid”消息。

=TEXTJOIN(", ", TRUE, XLOOKUP($D2,NPC_Names,NPC_IDs,"NPC invalid",0,-1)&"")

我也尝试了TEXTJOIN和 FILTER，但返回的是空白单元格——单元格中没有错误信息，Excel的错误检查器也没有弹出。

=TEXTJOIN(", ", TRUE, FILTER(NPC_IDs, ISNUMBER(SEARCH($D2, NPC_Names)), ""))

我知道可以在FILTER中使用关键字而不是命名范围，但由于NPC的数量众多，这样做会很不实用。

解决方案

带有 TEXTJOIN 和 FILTER 函数的变体可以使用，你只需重新排列参数即可。此外，还需要在一个短语是另一个短语的一部分的情况下做好防护。
=TEXTJOIN(", ",,FILTER(NPC_IDs, ISNUMBER(SEARCH(", " & NPC_Names & ", ",", " & $D2 & ", ")), ""))