如何在EXCEL中对多行使用匹配？

Question

我想使用类似于EXCEL中的MATCH函数，只是我想在包含多行和多列的表中查找一个值。然后，该函数应返回找到的值所在的列。

为了更好地理解，这里有一张图片：

​

​

在单元格K6中，值是“鸡”。我想在从A1:G9的表格中查找“鸡”。然后，我希望将单元格M6中的值设置为"Protein Source"，因为这是表A1:G9中“鸡”的关联值。目前，在M6中有逻辑值"TRUE“，因为我使用COUNTIF来查看”鸡“是否在A1:G9中。但我不知道如何将“蛋白质来源”的值传递给M6。

如果我不需要查找多个行/列，MATCH至少会找到列/行号。但是匹配不接受多行/多列。

Answer 1

=INDEX($A$1:$G$1,SUMPRODUCT(COLUMN($A$2:$G$9)*($A$2:$G$9=K3)))

将它放在鸡肉旁边的M3中，你应该能够将它复制下来。

很抱歉，根据你的问题标题，我不能用MATCH做你想要的事情。但是，我可以返回在其中找到您的项目的列号。这个等式的一个主要警告是，无论你在A2:G9范围内寻找什么，都只能出现一次！如果它多次出现，即使没有错误，等式也可能会崩溃或返回不正确的列。

通常情况下，索引和匹配在一起工作得很好。你想要做的基本上是：

=Index (header row, MATCH(Item I am looking for, Table of stuff))

所以我所做的就是用SUMPRODUCT替换了这个想法中的匹配部分：

SUMPRODUCT(COLUMN($A$2:$G$9)*($A$2:$G$9=K3))

基本上，COLUMN将返回我们在一个计算实例中查看的列数，然后在计算过程中给出下一个列数。无论列数是多少，乘以一个逻辑语句，如果为false，则为0；如果为true，则为1。因此，任何不具有我们查找的列号的列号都等于0，并且只有具有我们查找的列号的列号才会被返回，因为它乘以1。

问题是，如果你有多次鸡肉，你会得到不止一次的列号，它们会被加在一起，这意味着例如，你的豆浆在第6列中有两次，返回值6+6 = 12。问题是我们没有12列，所以我们得到了一个错误。另外，如果你有两个香蕉，你会有1+1= 2，并且你会返回蔬菜的第二列中的头。

​

​

如果你想实现一些潜在的错误检查，你可以在等式中使用一些IFERROR和ERROR.TYPE。它不会捕获列号中的重复项。你最终会得到如下的结果：

=IFERROR(INDEX($A$1:$G$1,SUMPRODUCT(COLUMN($A$2:$G$8)*($A$2:$G$8=K3))),IF(ERROR.TYPE(INDEX($A$1:$G$1,SUMPRODUCT(COLUMN($A$2:$G$8)*($A$2:$G$8=K3))))=3,"NOT FOUND","MULTIPLE ENTRIES"))

然后结果如下所示：

​

​

旁注

顺便说一句，如果您的表不在A列中，您将需要从列等式中减去一些数字，这样第一列减去幻数就会得到1...or。您可以这样做，以使其更具动态性(这是技术术语，对吗？)：

SUMPRODUCT((COLUMN($A$2:$G$9)-COLUMN($A$2)+1)*($A$2:$G$9=K3))

Answer 2

如果存在有限数量的行，从一系列MATCH functions接收其列号的INDEX/MATCH函数对可以适合于基于标准公式的解决方案。如果没有找到，将使用IFERROR function将控制传递给下一个MATCH语句来检查每一行。

在M6中作为标准公式，

=INDEX(A$2:G$2, IFERROR(MATCH(K6, A$3:G$3, 0), IFERROR(MATCH(K6, A$4:G$4, 0),
                IFERROR(MATCH(K6, A$5:G$5, 0), IFERROR(MATCH(K6, A$6:G$6, 0),
                IFERROR(MATCH(K6, A$7:G$7, 0), IFERROR(MATCH(K6, A$8:G$8, 0),
                IFERROR(MATCH(K6, A$9:G$9, 0), MATCH(K6, A$10:G$10, 0)))))))))

​

这种方法似乎适合您的样本数据，但随着食品数据行数的增加，这种方法显然有其局限性。事实上，在某些情况下，覆盖A:G列而不是第3:9行可能是有利的。

Answer 3

最好的方法之一是使用Linq作为一种搜索方法。

是一个很好的入门链接！

另一种方法是手动循环遍历行和单元格，这是一个缓慢而令人讨厌的过程，并比较这些值。

我知道你可能想要一个直截了当的答案，但我认为使用LINQ做你想做的事情会让你受益更多，并且保持简洁。

在谷歌上搜索"Use LINQ for excel“可以给你提供很多指导。

示例链接：

在Excel2007 https://msdn.microsoft.com/en-us/library/dd920313(v=office.12).aspx中使用LINQ

用于数据搜索https://www.youtube.com/watch?v=_4aNwh7pRxo的视频链接到Excel