时间戳

首页 > 解决方案 > 如何从包含 Excel 中的单词/文本的单元格返回多个匹配值

excel - 如何从包含 Excel 中的单词/文本的单元格返回多个匹配值

问题描述

我有一堆由身份证号和姓名组成的数据。

图 1

在此处输入图像描述

{=IF(ISERROR(INDEX($A$2:$B$12;SMALL(IF($A$1:$A$16=$E$1;ROW($A$1:$A$12));ROW(1:1));2));"";INDEX($A$1:$B$12;SMALL(IF($A$1:$A$16=$E$1;ROW($A$1:$A$12));ROW(1:1));2))}

以前（图 1）我成功地能够从仅包含一个名称的列名称中返回具有名称值的多个值。我正在使用索引函数数组公式来解决这个问题。

但是，当我在该名称列中有多个名称时，我被卡住了。我想要做的是在不修改该列的情况下返回多个 Id Number 值，其中包含多个名称，在 Names 列中用“逗号”分隔。预期结果如图 2 所示。

图 2

在此处输入图像描述

问题是：

  1. 我想根据包含名称的名称列从 ID 编号列中获取值
  2. 我想自动获取多个值，就像在图 1 中显示的那样
  3. 如果有两个 ID Number 值相同，则将其删除或不显示在结果中。

我不介意你们对我采取的任何方法。我将感谢您提供的任何解决方案。非常感谢。

标签： excelindexingmatchvlookup

解决方案

您可以使用

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(FIND(E$1,$B$2:$B$11))*(COUNTIF(E$1:E1,$A$2:$A$11)=0),ROW($B$2:$B$11)),1)),"")

使用作为数组公式输入CtrlShiftEnter

或者

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$11)/(ISNUMBER(FIND(E$1,$B$2:$B$11))*(COUNTIF(E$1:E1,$A$2:$A$11)=0)),1)),"")

正常输入。

在此处输入图像描述

编辑

@Ron Rosenfeld 是绝对正确的，他们所代表的公式将匹配（例如）Jo 和 John，尽管由于他们使用区分大小写的 find 并且每个开头都带有大写字母，这种影响在一定程度上有所缓解名称（因此 Ange 与 Hanger 不匹配）。

修改后的公式将是

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(FIND(","&E$1&",",","&$B$2:$B$11&","))*(COUNTIF(E$1:E1,$A$2:$A$11)=0),ROW($B$2:$B$11)),1)),"")

和

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$11)/(ISNUMBER(FIND(","&E$1&",",","&$B$2:$B$11&","))*(COUNTIF(E$1:E1,$A$2:$A$11)=0)),1)),"")

推荐阅读