Excel 365工作表数组-未找到返回值的公式？

Question

哪些数组公式将返回不出现在另一个列表中的值？

示例:名为ShortList的单元格包含(每个单元格一个单词)：

How recent  neuroscience    research    points  the way towards defeating   adversarial examples    and achieving   a   more    resilient   consistent  and flexible    form    of  artificial  intelligence

名为LongList的单元格包含：

a   about   above   across  and how however i   instead more    neither never   nevertheless    next    no  of  second  several shall   she the towards

下面是公式的预期输出：

recent  neuroscience    research    points  way defeating   adversarial examples    achieving   resilient   consistent  flexible    form    artificial  intelligence

我认为它可能涉及，匹配，xmatch，查找，或xlookup。

Answer 1

我想我明白了。这将返回我问题中的预期结果。

=FILTER(ShortList, ISNA(MATCH(ShortList, LongList, 0)))

希望能看到其他的答案。

Answer 2

当您的FILTER()公式正常工作时，但是对于大型数据集，LET()可能给您提供比Microsoft文档快两倍的性能

=LET(x,A1:A3,y,ISNUMBER(MATCH(x,B1:B10,0)),FILTER(x,y=FALSE))

名字经理试着-

=LET(x,ShortList,y,ISNUMBER(MATCH(x,LongList,0)),FILTER(x,y=FALSE))

​

​