如果满足条件,则自动从另一个表填充一个表

如果满足条件,则自动从另一个表填充一个表

我有两个表:信息和列表。

信息表有两列:姓名和性别。

列表表也有两列:男性和女性。

我正在尝试使用公式自动将男性或女性的姓名填充到列表表中。

INDEX()我尝试使用MATCH()

=INDEX(A:A, MATCH(D1, B:B, 0),1)

但它只返回与性别对应的名字。

有任何想法吗?

答案1

由于匹配的数据跳过行,因此您不能使用简单的索引/匹配公式来提取名称(即,不使用辅助列)。

最简单的解决方案需要使用数组公式:

工作表截图

数组中输入(Ctrl++ ShiftEnter以下公式D2并将其复制粘贴/向下填充/向右填充到表格的其余列中DE不要忘记删除{}):

{=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE),ROW()-1)),"")}

该公式的工作原理是,如果性别匹配,则首先构建一个包含行索引的数组,否则FALSEIF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE)

然后使用该SMALL()函数提取 List 表数据行对应的下一个最小索引:SMALL({…},ROW()-1)。此方法有效的原因是该SMALL()函数忽略了布尔值。

请注意,如果在除行之外的任何行中输入公式2,或者在输入公式后在第一个 List 表数据行上方插入/删除行,则ROW()-1需要调整部分以使第一个数据行的结果为 1。

最后,使用该索引提取适当的名称:。INDEX($A:$A,<next smallest index>)

只是IFERROR()为了隐藏当函数用尽有效索引#NUM!时发生的错误。SMALL()


该公式的版本更加强大但更加复杂,它可以自动调整信息表中的数据行数,并且如果在列表表的第一个数据行上方插入/删除行,也不会中断,如下所示:

{=IFERROR(INDEX($A:$A,SMALL(IF($B$1:INDEX($B:$B,COUNTA($B:$B))=D$1,ROW($B$1:INDEX($B:$B,COUNTA($B:$B))),FALSE),ROW()-ROW($B$2)+1)),"")}

相关内容