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

问题描述

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

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

我在数组 C2:E5 中寻找 Chevrolet.一旦确定雪佛兰在 D 列中，我需要它返回 D1 中的值.如果它在 E 列中，我需要它返回 E1 中的值.

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.

推荐答案

试试这个数组公式:

=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))

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

Enter formula in any cell by pressing 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}.
• 而且我们已经接近我们需要的东西，因为我们已经有了一个列和巨大数字的数组.我们只是使用 SMALL 来返回最小的数字，在我看来，它是找到匹配项的最低 列号.所以 SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1) 将返回 2. Chevrolet 在 $C$1:$E$1 中引用的列.
• 因为我们已经有了列号，所以我们只需使用 INDEX 函数，即:INDEX($C$1:$E$5,1,2).

• 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).

注意: 99^99 可以是任何相对较大的数字.不一定 99^99.可以使用实际 16385(Excel 2007 及更高版本中的最大列数 + 1).

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.

结果:

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