Excel中一对多查找，绝对不能错过的高级技能！

---

在Excel中进行一对多查找，一直是很多人心中的痛。

所以今天罂粟姐姐给大家分享两组函数组合，帮助大家熟练掌握一对多查找这一高级职场技能。

案例：

现有学生成绩单一份。

图：原始数据

现需要在I4-N9区域根据I2生源地查找所有该生源地的记录。

图：预计实现效果

方法1：

VLOOKUP COUNTIF

操作步骤：构造辅助列A列，在A2单元格输入公式=COUNTIF(C$2:C2,C2)&C2，用COUNTIF函数将生源地出现的次数和生源地联系起来，形成序号 生源地的形式。

 

图：构造辅助列

在I5单元格输入公式

=IFERROR(VLOOKUP(ROW(A1)&$I$2,$A:$G,COLUMN(B1),0),'')

横向、纵向进行单元格填充，即完成一对多查找。

 

图：完成一对多查找

方法2：

INDEX SMALL

这种方法在上周一教程中提到过，再次单独拿出来给大家分享，可以与方法1进行对比，加深记忆。

计算方式：在D5单元格输入公式=INDEX($B:$B,SMALL(IF($A$2:$A$10=$D$2,

ROW($A$2:$A$10),4^8),COLUMN(A1)))&''，按Ctrl Sheet Enter三键结束运算，向右拖动公式即可完成同一生源地学生名单的查找。

其中，利用SMALL函数来定位所有D2在第一列的位置，COLUMN(A1)用来显示第几个D2，这样在拖动D5单元格填充柄往右填充公式时，在D5时为COLUMN(A1)即1，第一个D2；E2时为COLUMN(B1)即2，第二个D2，以此类推。在这个公式末尾，添加&''，是为了实现公式在向右拖动的过程中如果没有匹配值就用空格代替。

图：INDEX与SMALL函数嵌套实现一对多查找