经常被问道Excel中如果实现一对多查找，我多多少少写过不少了，但是不够完善，今天我们就抽点时间，写过专题，彻底总结一下！这么多方法和思路，你会几种？

最后一种，专门为小白准备，保证人人学得会的方法~！

全部干货，其实每一种方法，都值得我们单独开一篇文章，好好聊聊，泡壶茶，我们开始……

01 传统方法 | 万金油套路，版本通用！

根据班级查询对应的全部名称，一般我们使用下面“万金油”套路，各版本通用，也是目前觉得此类问题最多的方法！

▼万金油套路

=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$F$1,ROW($A$2:$A$10)),ROW(A1))),"")

虽然我专门出过教程讲解，但是对于新手来说难度还是很大！

拓展阅读：

--> ☆经典公式解读 | 庖丁解牛Excel"万金油"公式

这是因为我们把很多处理步骤一起处理了，嵌套公式导致理解困难，其他很多时候，我们可以分步处理，让问题变得简单！

02 辅助列 | 化繁为简，分步搞定

STEP01 | 添加辅助列

我们对要筛选的班级从上往下计数，这样一对多问题，就变成了1对1

大家观察第1班数据所在行会发现，变成第1班 | 计数

=B2&"|"&COUNTIF($B$2:B2,$G$1)

如果你觉得上面其他班级干扰不好理解，可以添加IF判断，不过理解了是没有必要的！单纯了为了提高可读性！

=IF(B2=$G$1,B2&"|"&COUNTIF($B$2:B2,$G$1),"")

这里我们也兼顾一下0基础的同学，如果不懂COUNTIF 递增，还是引用方式基础没掌握

拓展阅读：

--> ☆ 基础 | Excel中单元格的引用方式，读这篇就够了！

STEP02 | 使用VLOOKUP+ROW一对一查询

▼VLOOKUP基础入门用法

=IFERROR(VLOOKUP($G$1&"|"&ROW(A1),$A:$D,COLUMN(C1),),"")

03 FILTER函数 | 筛选函数，O365专属！

避免部分高版本同学来杠，我们还是写一些O365+更简洁的套路，不过版本限制

▼不用羡慕，365专属

=FILTER(B2:C10,$A$2:$A$10=F1)

第一个参数是需要返回的区域，第二参数是条件过滤！O365引入了动态数组概念，不用三键，且自动扩展！

这些护发公式，写过专题：

>> Excel中那些护发公式！（上篇）

>> Excel中护发公式-下篇，增强版！

O365引入了一些革命性的东西：

>> 数组三键或成历史，这是一场真正的EXCEL革新

04 VBA | 凡我所想，一念即达！

使用Change事件，一旦F1内容修改就会触发代码执行！

▼ 动画演示

VBA源码：复制在对应的工作表VBE

'公众号：Excel办公实战 作者：E精精
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr, brr(1 To 1000, 1 To 2)
    Application.EnableEvents = False '禁止反复触发
    If Target.CountLarge > 1 Then End
    If Target.Address(0, 0) = "F1" Then
        Range("E4:F100").Clear '清空历史数据
        arr = Range("A1").CurrentRegion.Value
        For i = 1 To UBound(arr)
            If arr(i, 1) = Target Then
                n = n + 1
                brr(n, 1) = arr(i, 2)
                brr(n, 2) = arr(i, 3)
            End If
        Next
        Range("E4").Resize(n, 2) = brr
    End If
    Application.EnableEvents = True '禁止反复触发
End Sub

如果上面的你都会，那么你的Excel水平应该中级的水平了吧，恭喜！

但是我们还有一些方法，分享给您，让我们继续来看看吧！或者也能帮你重新认识一下你熟悉又陌生的Excel！

05 Power Query | 数据处理申请，简单高效！

▼ 详细动画演示

具体实现过程：

首先，我们把数据都加入到PQ中，先创建超级表，修改名称，方便处理！

选择任意一个表格，依次点击，加载数据到PQ编辑器，另一个表，一样处理！

右击深化出筛选的年纪，同时修改查询名称为班级

在Data查询中，输入M函数即可

= Table.SelectRows(源,each [班级]= 班级)[[姓名],[成绩]]

Table.SelectRows 如其名，输入Table类，作用就是根据条件筛选表格，跟表格中的筛选功能差不多，只不过通过函数来呈现，第一参数是表，第二参数是筛选条件！

关闭并上载到工作表中即可！

我们还有其他方法吗？当然还有，我们可以使用SQL处理！

06 SQL | 结构化查询，我是被迫的！

通过【现有连接】加载数据到工作表

写SQL

select 姓名,成绩 from ['方法07-SQL$'] where 班级="第2班"

SQL一般是数据库的标配，不过Excel也可以使用SQL，对比标准的数据结构，我们也经常会考虑使用SQL处理！

注意一些在Excel是使用SQL 表名后面需要加上$符号，一般数据库是不需要的~ 不过如果该区域你已经定义了名称，使用名称,不需要$!

07 数据透视表 | 大道至简，万法归一！

把【班级】拖放到筛选区域，就可以按照班级筛选对应的数据！没有比这更简单的方法了！

除了这些方法，我们还可以通过VBA来实现，对于懂VBA的同学来说也是非常简单的

今天你学废了吗？你会哪几种方法？

看完就是学会了！收藏夹中可以，请不要让他吃灰太久！

【收藏】【点赞】【转发】【在看】鼓励一下这么“肝”的小编！

Excel一对多查找方法大全