Excel-在数组中查找值并返回相应列的内容

Beckie 发表于 Dev

贝基

我试图在数组中找到一个值，然后在相应列的特定行中返回该值。

在下面的示例中，我需要知道雪佛兰位于哪个海湾：

    Column A        Column C    Column D    Column E
    Chevrolet       Bay 1       Bay 2       Bay 3
                    Toyota      Ford        Saturn
                    Honda       Chevrolet   Jaguar
                    Ferrari     Subaru      Lexus
                    Mitsubishi  Hundai      BMW

我正在阵列C2：E5中寻找雪佛兰。一旦确定雪佛兰在D列中，我就需要它返回D1中的值。如果它在E列中，则需要它返回E1中的值。

任何帮助将不胜感激。提前非常感谢您。

L42

试试这个数组公式：

=INDEX($C$1:$E$5,1,SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1))

或者，如果您确定每一列都包含正在搜索的内容，则可以这样编写：

=INDEX($C$1:$E$5,1,SMALL(IF($C$1:$E$5=A1,COLUMN($A:$C),99^99),1))

通过按Ctrl+ Shitf+在任何单元格中输入公式Enter。

它是如何工作的？
我们的最终目标是找到包含匹配项的列：

首先，我们使用以下公式搜索匹配项：SEARCH(A1,$C$1:$E$5)。它只是检查是否有任何条目匹配A1。实际上，它可以简化为，$C$1:$E$5=A1但是我不确定每列中的所有条目是否都与A1中的内容完全匹配。
当作为数组公式输入时，该公式将生成一个值数组。类似于：{SEARCH(A1,C1), SEARCH(A1,D1), SEARCH(A1,E1);... SEARCH(A1,E5)}。结果将是数字数组和错误（如果未找到）。但是我们不想要那样，否则我们每次都会返回错误。
然后，我们使用IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99)。如果存在匹配项，则此公式返回“列号”，否则返回相对较大的数99^99。结果将是：{99^99, 99^99, 99^99, 2, ..., 99^99}。
而且我们已经接近需要的条件，因为我们已经有了一个Column数组和一个庞大的数组。我们只是SMALL用来返回最小的数字，我认为这是找到匹配项的最小的列号。因此SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1)将返回2。这是在$ C $ 1：$ E $ 1中引用Chevrolet的列。
既然我们已经拥有的列数，我们简单地使用INDEX函数是：INDEX($C$1:$E$5,1,2)。