【小记】excel vlookup一对多匹配

应用概述

应用场景：学生报名考试，需要整理成指定格式，发给考试院。
一个学生最多报考四门

格式实例：准考证号   姓名   科目1   科目2  科目3  科目4

原数据报名表：

最后得到结果：

操作

预处理数据

创建两个新excel表。
表1：将各sheet里的所有数据，复制进去
表2：将各sheet里的所有 准考证号数据，复制进去
注：姓名可能存在同名，准考证号是唯一的。
表2中操作：选中列，重复项，使得每个准考证号只出现1次。

计数指令 COUNTIFS

在表1中，
step1 准考证号左边插入一列，进行计数COUNTIFS，用于后面vlookup匹配使用。

COUNTIFS($B$1:B1,B1)
注意是$B$1 固定引用，COUNTIFS表示B1出现了第几次

step2 B1基础上加上次数

=B1&COUNTIFS($B$1:B1,B1)

将去除过重复项 的表2，复制进表1

进行一对多匹配 vlookup

=VLOOKUP($H1&1,$A:$E,5,0)注意：是$H1,将H列锁定 $A$E
&1 ：准考证号在 A到E列 第1次出现，	匹配第5列的数据（即科目）
&2 ：准考证号在 A到E列 第2次出现，	匹配第5列的数据（即科目）
&3：准考证号在 A到E列 第1次出现，	匹配第5列的数据（即科目）
&4 ：准考证号在 A到E列 第2次出现，	匹配第5列的数据（即科目）

补充：如果存在匹配不到的，默认使用空

=IFERROR(VLOOKUP($H1&1,$A:$E,5,0),"")