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

Question

这是我的问题，我有一个包含两列的表格:产品引用和相应的通知 ID:

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

(编辑:在我的实际应用程序中，“产品引用”和“通知 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 的组合提取单个匹配项和 MATCH功能，例如:

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

(这对我没用，因为 A 列中的引用也是唯一的)

(顺便说一句，我不知道是否有更好的方法来处理 MATCH 未找到匹配时引发的错误)

1. 我可以使用(使用 Ctrl+Shift+Enter 激活的数组公式)将 B 列中对应于多个匹配项的元素提取并连接到 A 列中的单个引用:

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

(这对我没用，因为 A 列中的引用也是唯一的)

总结:我可以找到多个匹配项并将其合并到单个引用，但无法找到单个唯一匹配项并将其合并到多个引用(我想做的)。

---

失败的尝试

我尝试以不同的方式混合前面的公式以获得我想要的结果，但都因错误而失败。

• 组合 1、2 和 4(在 bool 匹配数组上使用 OR):

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

或(在匹配的 bool 数组上使用 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功能。

Answer 1

好问题。如果您只有 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))