我有两个表:信息和列表。
信息表有两列:姓名和性别。
列表表也有两列:男性和女性。
我正在尝试使用公式自动将男性或女性的姓名填充到列表表中。
INDEX()
我尝试使用MATCH()
=INDEX(A:A, MATCH(D1, B:B, 0),1)
但它只返回与性别对应的名字。
有任何想法吗?
答案1
由于匹配的数据跳过行,因此您不能使用简单的索引/匹配公式来提取名称(即,不使用辅助列)。
最简单的解决方案需要使用数组公式:
数组中输入(Ctrl++ Shift)Enter以下公式D2
并将其复制粘贴/向下填充/向右填充到表格的其余列中D
(E
不要忘记删除{
和}
):
{=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE),ROW()-1)),"")}
该公式的工作原理是,如果性别匹配,则首先构建一个包含行索引的数组,否则FALSE
:IF($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)),"")}