Excel如何在不使用VBA的情况下从逗号分隔列表中查找匹配元素的所有行

这是我的问题，我有一个包含两列的表：产品参考和相应的通知 ID：

 | A       | B      | C      | D      |
---------------------------------------
1| Product | Notice |        |        |
2| p1      | n1     |        |        |
3| p2      | n2     |        |        |
4| p3      | n3     |        |        |
5|         |        |        |        |
6|         |        | p1, p3 | =...   |

(edit:在我的现实生活应用程序中，“产品参考”和“通知 ID”列不是并排而是由其他列分隔开）

在另一个单元格（例如 C6）中，我有一个以逗号分隔的产品参考列表，比方说p1, p3我需要一个公式来输出相应的通知ID，即n1, n3在本例中，位于单元格 D6 中。

重要的：由于不同的原因，我无法使用VBA，我需要一个标准的excel数组公式。

这是我目前可以做的：

1. 与FILTERXML函数，我可以将逗号分隔的列表拆分为一个数组：FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")

2. 与TEXTJOIN函数，我可以将数组合并为字符串。

3. 我可以通过组合提取单个匹配项INDEX and MATCH函数，例如：

=IF(ISERROR(MATCH("p3"; A:A; 0)); "not found"; INDEX(B:B; MATCH("p3"; A:A; 0)))

（这对我来说没有用，因为 A 列中的引用再次是唯一的）

（顺便说一句，我不知道是否有更好的方法来处理由MATCH当没有找到匹配项时）

4. 我可以提取 B 列中与多个匹配项对应的元素并将其连接到 a单一参考在 A 列中（使用 Ctrl+Shift+Enter 激活数组公式）：

{=TEXTJOIN(", "; TRUE; IF(A:A="p2"; B:B; ""))}

（这对我来说没有用，因为 A 列中的引用再次是唯一的）

总之：我可以查找多个匹配项并将其合并到单个引用，但无法查找单个唯一匹配项并将其合并到多个引用（我想要做的）。

失败的尝试

我尝试以不同的方式混合以前的公式来得到我想要的，但都失败并出现错误。

• 结合 1、2 和 4（使用OR在匹配的布尔数组上）：

{=TEXTJOIN(", "; TRUE; IF(OR(A:A=FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")); B:B; ""))}

或（使用SUM在匹配的布尔数组上）：

{=TEXTJOIN(", "; TRUE; IF(SUM(A:A=FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")); B:B; ""))}

在这里，我不确定如何处理中考虑的不同数组IF（A 列和参考文献列表由FILTERXML).

• 结合 1、2 和 3：

{=TEXTJOIN(", "; TRUE; INDEX(B:B; MATCH(FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s"); A:A; 0)))}

在这里，我不确定如何再次处理 (i) 所考虑的不同数组（A 列和由FILTERXML), (ii) 引起的误差MATCH当没有找到匹配时，(iii) 数组引用传递给INDEX功能。

好问题。如果您只有 Excel 2019，您可以选择：

公式为E1:

=TEXTJOIN(", ",,IFERROR(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(D1,", ","</s><s>")&"</s></t>","//s"),A:B,2,FALSE),""))

如果您有 Excel O365，那么也许：

=TEXTJOIN(", ",,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(D1,", ","</s><s>")&"</s></t>","//s"),A:A,B:B,"",0))