Excel - 在数组中查找一个值并返回相应列的内容 [英] Excel - Find a value in an array and return the contents of the corresponding column

问题描述

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

 列A列C列D列E 
雪佛兰湾1湾2湾3 
丰田福特土星
本田雪佛兰捷豹
法拉利斯巴鲁雷克萨斯
三菱Hundai宝马
  pre> 
 
 
我正在寻找雪佛兰C2：E5。一旦确定雪佛兰在D列，我需要它返回D1中的值。如果在列E中，我需要它返回E1中的值。
 
 
 
任何帮助将不胜感激。谢谢你提前。
解决方案
尝试这个数组公式：
 
 
 
  = 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  + 输入。
 
 
 
 它是否工作？ 
 
我们的最终目标是找到包含匹配的列：
 
 
 
 
 
首先，我们使用以下公式搜索比赛：搜索（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））），柱（$ A：$ C），99 ^ 99）。如果有一个匹配，并且相对较大的数字 99 ^ 99 则此公式返回列号。结果将是： {99 ^ 99，99 ^ 99，99 ^ 99，2，...，99 ^ 99} 。
 
 <我们已经接近我们需要的，因为我们已经有一个数组列和巨大的数字。我们只需使用 SMALL 返回最小的数字，这在我看来是找到一个匹配的最小的列号。所以 SMALL（IF（NOT（ISERROR（SEARCH（A1，$ C $ 1：$ E $ 5））），COLUMN（$ A：$ C），99 ^ 99），1）将返回2.哪里是 Chevrolet 在<$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $我们已经有列号，我们简单地使用 INDEX函数，它是： INDEX（$ C $ 1：$ E $ 5,1,2）。  
 
 
 
 
 注意：  99 ^ 99 可以任何比较大的数字。不一定 99 ^ 99 。 
 
 
 
 结果： 
  
 
I am trying to find a value within an array and then return the value in a specific row in the corresponding column.

In the example below, I need to know which bay the Chevrolet is in:

    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
I am looking for Chevrolet in the array C2:E5.  Once it determines that the Chevrolet is in Column D, I need for it to return the value in D1.  If it was in column E, I need it to return the value in E1.

Any help would be greatly appreciated.  Thank you so much in advance.
解决方案
Try this Array Formula:

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

or if you are sure that each column contains exactly what's being searched it can be written like this:

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

Enter formula in any cell by pressing Ctrl+Shitf+Enter.

How does it work?

Our ultimate goal is to find the Column that contains the match:


First we did the search for the match using this formula: SEARCH(A1,$C$1:$E$5). It just checks if any of the entries matched A1. Actually, it can be simplified to $C$1:$E$5=A1 but I'm not sure if all entries in each column match exactly what's in A1.
That formula will produce an array of values when entered as array formula. Something like: {SEARCH(A1,C1), SEARCH(A1,D1), SEARCH(A1,E1);... SEARCH(A1,E5)}. The result will be array of number(s) and error (if non was found). But we don't want that, else we will be returning error everytime.
We then use IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99). This formula returns the Column Number if there is a match and a relatively huge number 99^99 otherwise. Result would be: {99^99, 99^99, 99^99, 2, ..., 99^99}.
And we are close to what we need since we already have an array of Column and huge number. We just use SMALL to return the smallest number which in my opinion is the lowest Column Number where a match is found. So SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1) would return 2. Which is the column where Chevrolet is referenced at $C$1:$E$1.
Since we already have the column number we simply use  INDEX Function which is: INDEX($C$1:$E$5,1,2). 


Note: 99^99 can be any relatively large number. Not necessarily 99^99. Actual 16385(max column number in Excel 2007 and up + 1) can be used.

Result:


                        
这篇关于Excel  - 在数组中查找一个值并返回相应列的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！