工作中，有时候我们需要把某个部门的人员信息单独调用出来，有些人会说，直接筛选不就可以了吗？如果只是想单纯的查看个人信息，直接用筛选确实更快捷。
我们今天要讲的是，办公室人员在填写文件审批单时，拟稿的部门涉及多个，我们要根据选择的部门，用数据有效性列出这个部门的人员，如图1，我们选择人事部后，拟稿人这里只显示人事部的人员姓名，以此类推。
图1
系统共有2个表册，第一个表为“员工基本信息”，主要内容有员工姓名、部门等；第二个表为“部门人员”，主要实现上述功能。
下面我们依次讲解。
一、增加辅助列处理员工基本信息里重复显示的部门
首先看图2，因为一个部门一般不可能只有一个人，所以一人显示一次部门的话，会重复显示，这时候我们需要增加辅助列，用函数把重复的部门进行去重，然后自定义名称，在第二表用“数据有效性”进行调用。
图2
此例，我们要用IFERROR、INDEX、MATCH、COUNTIF函数组合，计算出某列单元格不重复的值，然后用IFERROR函数隐藏错误值。
IFERROR函数：如果计算结果为错误值则显示指定的值。
方法：IFERROR(计算结果,如果计算结果为错误值显示指定的值)
INDEX函数：返回表或区域中的值或值的引用。
常用方法：INDEX(返回哪个区域的值,在第几行或第几列)
MATCH函数：返回指定数值在指定数组区域中的位置。
常用方法：MATCH(需要查找的值,在哪里查找,精准查找还是模糊查找)
COUNTIF函数：对指定区域中符合条件的单元格进行计数。
用法：COUNTIF(区域,条件)
下面，在E3单元格输入函数：
=IFERROR(INDEX($C$3:$C$22,MATCH(0,COUNTIF(E$2:E2,$C$3:$C$22),0)),"")
图3
公式讲解：
COUNTIF(E$2:E2,$C$3:$C$22)
在$C$3:$C$22区域内查找E$2:E2出现的次数，返回的是数组。
MATCH(0,COUNTIF(E$2:E2,$C$3:$C$22),0)
在上面产生的数组中查找第一个为0的位置。
INDEX($C$3:$C$22,MATCH(0,COUNTIF(E$2:E2,$C$3:$C$22),0))
上述第一个为0的位置反馈到$C$3:$C$22区域内的值，如果没有则返回空值。
我们刚才说过，一个部门一般不可能只有一个人，所以复制到最后一行时，去重后中间或后面的单元格值就会出现错误值，我们只需要用IFERROR函数即可解决，如果显示的值为错误值，那我们就让他显示为空值，在EXCEL函数中空值的表示方法直接用""即可。
函数输入结束后，如果直接按回车键，将无法显示正确结果，因为这是数组公式，所以要按Ctrl+Alt+Enter键结束，然后拖动复制到最后一行，这样就得到了图2所示的效果。
二、在“部门人员”表调用部门名称并列出人员信息
第一步：设置“有效性”调用去重后的部门
图4
图5
选择B2单元格，在“数据”选项卡下找到“有效性”，打开对话框，在“设置”功能组“有效性条件”—“允许”选择“序列”，其他默认，在“来源”下方输入：=基本信息!$E$3:$E$10（表示“基本信息”表辅助列E列去重后的部门数据），然后确定即可点击鼠标选择切换部门。
第二步：用函数列出选择部门的所有人员信息
此例，我们要利用INDEX+SMALL+IF函数实现一对多查询，计算出某列单元格不重复的值。
在B3单元格输入函数：=INDEX(基本信息!B:B,SMALL(IF(基本信息!$C$3:$C$22=$B$2,ROW(基本信息!$C$3:$C$22),2^10),ROW(1:1)))，按Ctrl+Alt+Enter键结束，然后拖动复制到最后一行。
公式讲解：
IF函数：返回一个一维数组。
用法：IF(条件,符合条件，不符合条件）。
IF(基本信息!$C$3:$C$22=$B$2,ROW(基本信息!$C$3:$C$22),2^10)
如果B2单元格值与“基本信息”表C列值相同，则反馈“基本信息”表C列单元格的行号，如果为假，则返回2^10，表示2的10次方行，所以IF函数最终返回值要么是指定单元格的行号，要么是2的10次方行（数据量较大时要具体分析）。
SMALL函数：返回数组中第N个最小值。
用法：SMALL（数组区域,N)。
此处将IF函数返回的数组作为SMALL的第一参数，返回数组中第ROW(1:1)个最小值，即第一个最小值，往下复制公式得到ROW(2:2)，将第一步产生的数组中符合条件的行号从最小的开始连续输出，以此类推，所以SMALL函数最终返回的是IF函数里的行号。
至此，我们的操作就完成了，选择不同的部门，部门下方将列出本部门所有人员姓名，如果还需要其他信息，按照上面的方法操作即可。
图6
图7
提示：如出现0值，可在公式最后写&""，主要是为了避免0值得出现，也可将公式嵌套到IFERROR中。
