☰

Excel INDEX MATCH 检查多列

tag-icon • tag-icon microsoft-excel vlookup array
Excel INDEX MATCH 检查多列

我本质上试图解决的问题是使用 VLOOKUP 检查 A:E 列中的值，如果在其中任何一列中找到该值，则返回 F 列中保存的值。

由于 VLOOKUP 无法胜任这项任务，我研究了 INDEX-MATCH 语法，但我不知道如何针对值数组（而不是单个列）完成此操作。我在下面构建了一个示例数据集来尝试解释这一点：

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

如果被检查的单元格包含 1、2、3、4 或 5，则公式的结果应为 Apple。如果是 12 或 13，则应返回 Banana，最后如果包含 14，则应返回 Carrot。

后半部分是因为引用的单元格不是单个值，而是整个表格本身。因此，此搜索将根据不同的值完成大量次。

为了演示，其他地方还有另一个表（如下所示）包含这些值。我试图让系统识别哪一行，从而识别与每列关联的“苹果、香蕉、胡萝卜”值中的哪一个。表格如下所示

你好 - - - - - -

1------（苹果）----

2------（苹果）----

12-----(香蕉)-

ETC。 - - - - - - - - -

括号中的值是公式计算这些值的地方。

答案1

您有许多不同的情况。让我们考虑一种情况：

在列中的某处A通过埃有且仅有一个单元格包含 13，返回列中单元格的内容F在同一行。

我们将使用“辅助”列。在G1进入：

=COUNTIF(A1:E1,13)

并复制下来。这样我们就可以识别行：

在此处输入图片描述
现在我们可以使用MATCH()/INDEX()：

选择一个单元格并输入：

=INDEX(F:F,MATCH(1,G:G,0))

在此处输入图片描述

如果“规则”发生变化，一行中可能出现多个 13，或者几行包含 13，我们将修改辅助列。

编辑＃1：

根据您的更新，第一步是提取硬编码的十三从“辅助”列中的公式中取出并将其放入自己的单元格中，（说H1）。然后，您只需更改单个单元格即可运行不同的案例。

如果表格中有大量案例，您可以创建一个宏来设置每个案例（更新H1）并记录结果。

答案2

根据我自己的研究和与@Gary'sStudent 的讨论，我使用的解决方案是为可能包含该值的每个可能的列创建一个 MATCH 公式，以及一个空白捕获“IFERROR”语句。

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

现在可以隐藏这些列以防止用户混淆/交互。

然后我创建了一个索引，将这些值累积为一个值，该值应与相关 ROW 匹配。同样，如果在表中找不到该值，则有一个检查（第一个 SUM）将其作为空白值输入。

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

索引匹配阵列 最后，我输入了一些条件格式公式，以确保用户能够识别并替换/删除任何重复数据。

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

这只是提示用户删除重复数据。

在此处输入图片描述

答案3

对于 H1 中的单个公式：

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

这是一个数组公式，因此我们需要将引用限制在数据集的大小内。所有这些都是INDEX(E:E,MATCH("ZZZ",F:F))这样做的。这将返回 F 列中包含文本的最后一行。然后将其设置为要迭代的最后一行。

@Gary'sStudent 方法避免使用数组公式，可能是所需的方法。随着数据集和公式数量的增加，计算时间也会增加。甚至到了某个时候，Excel 会崩溃。通常这需要几千次，但我想发出警告。

在此处输入图片描述

编辑

为了避免使用数组公式并仍为一个公式：

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

这是基于 OP 的回答，只是将该方法合并为一个公式。

此公式将忽略重复的条目并返回找到该数字的第一行。

而且因为它是非数组，所以全列引用不会对计算时间造成不利影响。

![在此处输入图片描述

答案4

另一种方法是基于辅助表，它表示这个“应该”首先如何构建。这将避免事后调试和更改繁琐的方程式，并且能够干净地解决不同数量的列，这与具有 5 个查找列的想法不同。

如果上面的内容在 Sheet1 中，则添加 Sheet2。在该位置放置四列：行、列、ID、名称

公式Row应该是（在伪代码中，“Last”表示“针对 Sheet2 中的上一行”）

=IF(Column = 1, Last row + 1 , Last row)

公式Column：

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

ID和中的公式Name：

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

然后你把它向下填充（基本上直到row>原始表中的行数）。

最后，您将新表与普通的 vlookup 或 index/match 一起使用。

优点：公式更简单，更易于使用和理解。

缺点：需要额外的表，必须保持表的长度。性能方面存在风险，因为这几乎需要单个线程来处理整个“字符串”值。

此外，如果几个错误行都没问题，代码可以稍微简单一些，可能性能更高，然后我们可以假设列数始终为 5，给出行和列。

相关内容