索引匹配，其中任何查找值都在查找数组中 [英] Index match where any lookup values are in lookup array

问题描述

是否可以执行索引匹配来查找查找值中的任何字是否在查找数组中。查找值是一个句子，因此可以包含多个单词。

Is it possible to do an Index Match to find if any of the words that are in the lookup value are in the lookup array. The lookup value is a sentence so can contain several words.

在下面的示例中，输出将是 B

In the example below the output would be B

LOOKUP VALUE = "FISH CAT HORSE..."

LOOKUP ARRAY
DOG  A
CAT  B
FROG C

提前谢谢。

推荐答案

我喜欢使用AGGREGATE将行号返回到INDEX（）：

I like to use AGGREGATE to return the row number to an INDEX():

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$4)/(ISNUMBER(SEARCH($A$2:$A$4,C1))),1)),"Not Found")

AGGREGATE是一个数组类型公式，因此引用应受到限制只有那些具有数据而不是使用完整的列引用。

AGGREGATE is an array type formula and therefore the references should be limited to only those with data and not use full column references.

对于更动态的公式，我们可以使用INDEX / MATCH动态地结束数据集。这将允许搜索参数根据数据集中的值的数量增长或缩小，从而将迭代次数限制为只有具有数据的单元格。

For a more dynamic formula we can use INDEX/MATCH to set the end of the data set dynamically. This will allow the search parameters to grow or shrink depending on the number of values in the data set, thus limiting the number of iterations to only those cells with data in them.

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:INDEX(A:A,MATCH("ZZZ",A:A)))/(ISNUMBER(SEARCH($A$2:INDEX(A:A,MATCH("ZZZ",A:A)),C1))),1)),"Not Found")

这篇关于索引匹配，其中任何查找值都在查找数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！