☰

INDEX/MATCH 公式仅吐出一个值

tag-icon • tag-icon microsoft-excel worksheet-function
INDEX/MATCH 公式仅吐出一个值

我有下面这张表。在同一工作表的另一个选项卡中，我想从“名称”列中提取所有与“状态”匹配为 NotFound 的值。我的公式如下所示：

=INDEX('nameoftab'!A:A, MATCH("NotFound", 'nameoftab'!C:C, 0))

但是公式只会提取它找到的第一个值，即 Laura。我该如何提取简单列表中的剩余值？我到处都找过了，但没找到简单的解决方案，请帮忙！

原始表

答案1

=INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1))

该公式假定图片中的表格位于名为“nameoftab”的工作表上，并覆盖范围 A1：C10。

将其放置在您希望第一个名字出现的位置，并根据需要复制下来。如果您想让它在复制到符合条件的名称列表之外时显示空白而不是错误消息，请将整个内容包装在 IFERROR 函数中：

=IFERROR(INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1)),"")

注意，由于 AGGREGATE 的公式 14 执行类似数组的计算，因此请避免在数组中使用完整列引用。对 INDEX 使用完整列引用是可以的，因为它不在 AGGREGATE 函数内。

答案2

为了解决这个问题，你需要反向查找公式：

在此处输入图片描述

怎么运行的：

  • 我假设您在 Range 中有 A3:C11数据Sheet1。
  • 写下标准未找到在Sheet2，A1。

  • 在 中输入此公式Sheet2， A3并填写。

    =IF(Sheet1!C3=$A$1,VLOOKUP($A$1,CHOOSE({1,2},Sheet1!C$3:C$11,Sheet1!B3:B$11),2,0),"")

注意：

  • 在上面的公式中，E1是查找值。C3:C11是公式在其中查找查找值的列，B3:B11是公式在其中查找目标值的列。

  • 和CHOOSE{1,2} 1&2是 Column Index 考虑 ColC和B。并2设置 Column Index 以从 Column 获取 Name B。

編輯：

如果你想要这样的输出，那么你可以使用数组（加拿大教育标准局)公式如下所示：

在此处输入图片描述

{=IFERROR(INDEX(Sheet1!$B$3:$B$11,SMALL(IF($A$1=Sheet1!$C$3:$C$11,ROW(Sheet1!$C$3:$C$11)- MIN(ROW(Sheet1!$C$3:$C$11))+1,""),ROW(A1))), "")}
  • 完成公式 Ctrl+Shift+Enter向下填充。

根据需要调整公式中的单元格引用。

相关内容