☰

查找某一行中匹配的值并返回列名 II

tag-icon • tag-icon microsoft-excel google-spreadsheets
查找某一行中匹配的值并返回列名 II

我的问题与在行中查找匹配的值并返回列名？

我有一个带有两个标签的电子表格：

  1. 选项卡 A 有一列是电子邮件地址列表（此选项卡中还有许多其他列）
  2. 选项卡 B 有 4 列，每列包含来自不同组的电子邮件地址。每列都有唯一的电子邮件地址（列之间没有重复）。因此，第 1 组在 A 列中有 50 封电子邮件，第 2 组在 B 列中有 100 封电子邮件，等等。

我想在选项卡 A 中的新列中插入一个公式，该公式查找选项卡 B 中 4 列中的每一列中的电子邮件，并将它们与选项卡 A 中的电子邮件地址进行比较。如果找到匹配项，则写入列标题。

非常感谢您的帮助>;-) 我愿意接受任何关于如何以不同方式设置以使其更容易的想法......

答案1

你也可以用以下方法得到更短的版本SUMPRODUCT：

=INDEX(Sheet2!$A$1:$D$1,SUMPRODUCT((A2=Sheet2!$A:$D)*1*{1,2,3,4}))

而且我还注意到，如果没有找到匹配项（就像在此答案之前发布的其他公式一样），它将返回公式本身的列索引（即，如果公式在 B 列中，它将返回 B 列标题的名称）。避免这种情况的一种方法是使用IF如下方法：

=INDEX(Sheet2!$A$1:$D$1,IF(SUMPRODUCT((A2=Sheet2!A2:D8)*1)=0,NA(),SUMPRODUCT((A2=Sheet2!A2:D8)*1*{1,2,3,4})))

稍微长一点^^;

答案2

以下数组公式应该有效。将其输入到选项卡 A 中新列的第 2 行单元格中，然后将其复制到选项卡 A 电子邮件列表的底部。

  =INDEX(Sheet2!$A$1:$D$1,1,SUM((A2=Sheet2!$A$2:$A$50)*1+(A2=Sheet2!$B$2:$B$100)*2+(A2=Sheet2!$C$2:$C$50)*3+(A2=Sheet2!$D$2:$D$50)*4))

作为数组公式，需要使用Control- Shift-Enter组合键输入。

为了分解公式的各个部分，假设选项卡 B 中只有一列电子邮件数据，例如 A 列：

  • $A2=Sheet2!$A$2:$A$50将标签 A 的 A2 单元格中的电子邮件与标签 B 的 B 列中的所有电子邮件进行比较，生成一个匹配结果数组，例如。由于标签 B 中的电子邮件是唯一的，因此数组中{False,True,False...False}最多只能有 1 个。True

  • 接下来，将该数组乘以 1 - ($A2=Sheet2!$A$2:$A$50)*1- 生成另一个数组“{0,1,0 ... 0}”，可以解释为“A2 中的值与选项卡 B 第一列数据范围第二行的值匹配。

  • 将该数组相加得出值 1，该值用于挑选出选项卡 B 的标题行第一列中的值：INDEX($A$1:$D$1,1,SUM(($A2=Sheet2!$A$2:$A$50)*1))。

  • 我们可以对标签 B 的 B、C 和 D 列进行相同的数组比较，将结果分别乘以 2、3 和 4，以表示标签 B 的第二、第三等列中的匹配。

答案3

您还可以使用非数组公式来执行此操作，使用 Countif() 而不是 A2=Range：

=INDEX(Sheet2!$A$1:$D$1,(COUNTIF(Sheet2!$A:$A,Sheet1!$A2)*1)+(COUNTIF(Sheet2!$B:$B,Sheet1!A2)*2)+(COUNTIF(Sheet2!$C:$C,Sheet1!A2)*3)+(COUNTIF(Sheet2!$D:$D,Sheet1!A2)*4))

相关内容