转载自百家号作者：Excel函数与VBA实例

在工作中我们经常会碰到根据某个单一条件去查找对应的数据值，这个时候我们常用的一个万能查询函数那就是vlookup函数，vlookup函数可以实现基本的向左、向右以及多条件值数据查询等功能。但是这个函数有个弊端就是，不能实现返回多个数据值。

如当我们在查询某个人当天所有门禁刷卡时间或当天人员的所有销售记录时候，从上往下查找只能查找出最上面的第一条数据，无法提取出整天的数据。如果要实现这个功能就需要用辅助操作来实现，会显得比较麻烦。那么今天我们就来讲讲自定义多功能查询函数和vlookup函数分别是如何解决这个问题的。

方法一、vlookup函数如何查找返回多个数据值

问题：提取张三7月1日所有刷卡记录

如上图效果图所示，当我们输入函数=VLOOKUP(ROW(A1),A:D,4,0)往下拖动，张三当天的所有刷卡记录都会显示出来，因为总共只有3条数据，所以第四条结果开始就会出现错误值。

操作方法：

第一步：首先用countif函数做一个辅助列，因为单纯的vlookup函数查询是无法返回多个数值的。插入A列，辅助列函数为：COUNTIF(C$2:C2,F$4)。

注意点：函数COUNTIF函数中C$2:C2，是非常有深意的，用相对引用的方式往下拖动，分别代表的数据区域则为：C$2:C3、C$2:C4、C$2:C5等。这样代表的意思就是可以查找出对应的人出现过多少次。

第二步：输入函数VLOOKUP(ROW(A1),A:D,4,0)进行数据查询，然后往下拖动即可返回姓名为张三的所有值。

注意点：vlookup函数第一参数使用ROW(A1)为条件值的目的是，通过对应姓名所在的数值来进行数据查询。比如第一条记录8:38分，选择函数ROW(A1)按F9，返回的是1；第二条记录10:15分，选择函数ROW(A1)按F9，返回的是2，以此类推。效果如下图所示：

方法二：自定义Mlookup多功能函数查找返回多个数据值

问题：提取张三7月1日所有销售单号

如上图效果图所示，输入函数：Nlookup(F4,C:D,2,-1)，即可返回张三7月1日销售的所有单号：2018070101,2018070106,2018070111，是不是感觉比vlookup函数更加简单神奇。这需要用到的是VBA代码来自定义一个Nlookup函数。

操作方法：

第一步：按alt+f11进入代码编辑窗口，新建一个模块；

第二步：输入以下代码后，保存为宏文件，即可使用自定义的Nlookup函数，如果你需要修改为其他自己喜欢的函数，可以全部替换即可。

代码如下：

Function Nlookup(rg, rgs As Range, L As Integer, M As Integer)

Dim arr1, ARR2, 列数

Dim R, n, K, X, cc, sr As String

arr1 = rg.Value

ARR2 = rgs

If VBA.IsArray(arr1) Then

For Each R In arr1

If R <> "" Then

cc = cc & R

列数 = 列数 + 1

End If

Next R

Else

cc = arr1

End If

If M > 0 Then '非查找最后一个

For X = 1 To UBound(ARR2)

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

K = K + 1

If K = M Then

Nlookup = ARR2(X, L)

Exit Function

End If

End If

Next X

ElseIf M = -1 Then '查找所有值

For X = 1 To UBound(ARR2)

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Nlookup = Nlookup & "," & ARR2(X, L)

End If

Next X

Nlookup = Right(Nlookup, Len(Nlookup) - 1)

Exit Function

Else '查找最后一个

For X = UBound(ARR2) To 1 Step -1

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Nlookup = ARR2(X, L)

Exit Function

End If

Next X

End If

Nlookup = ""

End Function

学习完上面的两种查询多个数据的方法，你现在认为哪一种方法更加简单了？当然这个多功能函数还包含有其他的功能，赶快尝试一下吧。

点击上方「 韩老师讲office 」可快速关注

敬请阅读与分享，让更多学习者一起进步！

问题来源

韩老师曾经写过一篇：Excel | VLOOKUP一对多查找：不连续相同内容对应的多个数据一次提取，今天有朋友说：这个公式写起来好麻烦啊！

我们的“office天天学”群里有位高手朋友说他更喜欢INDEX+SMALL,那韩老师今天就把INDEX+SMALL这一神组合完成一对多查找的方法给大家讲一讲。

结果是这样的：

公式实现

在F2单元格输入公式：

=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""，以三键组合结束。

公式分步解析

我们以查找“张二”的消费记录为例来分析：

第一步：

IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B))

用IF函数，建立一新的数组，这一新的数组建立的规则是：

如果A$2:A$13区域中的单元格内容等于E2单元格内容，则返回该单元格所在的行，否则返回整个工作表的行数。

所以：此部分返回的数组是：

{1048576;3;1048576;1048576;1048576;7;1048576;1048576;1013}

可以看到：凡是A列单元格内容等于张二的，返回的都是对应的行数，不等于张二的，返回的都是工作表的行数1048576。

第二步：

SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)

在第一步形成的数组中，查找第第一小的数值。

用ROW(A1)做SMALL函数的第二个参数，即第几小。

ROW(A1)是一个动态的数值，公式往下填充一行，行数加1，即当公式在F2单元格时，是 ROW(A1)，当公式填充到F3单元格是，是 ROW(A2)，当到F4单元格时，是 ROW(A3)……

这样，就在第一步的数组中找到了第1、2、3、4小的值，即3 、 7 、 10 、 13。

第三步：

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))

当公式在F2单元格时，返回B列第3行的值，即张二的第一次消费记录7478。因为公式中IF部分是数组计算，所以公式以三键组合结束。

公式向下填充，得到B列第 7 、 10 、 13行的值。

第四步：

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1))) &""

在最后加上&""，这一步是容错处理。 用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。

往期相关推荐

Excel144 | VLOOKUP与IFERROR是好搭档

Excel | VLOOKUP完成同一部门多个员工信息查询

Excel | 等级评定那些事：IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都能完成

Excel | 用VLOOKUP、INDEX函数、定义名称，制作带照片的信息查询表

Excel | DGET、SUMIFS、SUMPRODUCT、LOOKUP、OFFSET、VLOOKUP多条件查询

Excel | INDEX、VLOOKUP、HLOOKUP、LOOKUP排排站，查询函数任你选

Excel | LOOKUP函数――合并单元格拆分与查找计算的利器

Excel | LOOKUP查询函数十种用法大集锦，快快收藏了备用！

Excel | CHOOSE函数用法集锦――与SUM、VLOOKUP、IF、MATCH配合使用，查找更轻松

Excel | Vlookup逆向查询有问题，IF、CHOOSE来帮忙

转载自百家号作者：Excel函数公式

Excel中，函数公式的威力强大是众所周知的，但是，好多人还是应用不好，今天，我们对常用的万能公式，做一下讲解，希望对大家的工作有所帮助。

一、多条件计数统计——Countifs函数。

目的：计算销往“北京”地区且销量大于30的男销售员人数。

方法：

1、在目标单元格中输入公式：=COUNTIFS(C3:C9,">30",D3:D9,"男",E3:E9,"北京")。

解读：

1、Countifs函数为多条件计数函数。

2、语法结构：=Countifs(条件1范围，条件1，条件2范围，条件2……条件N范围，条件N）。

二、多条件求和统计——Sumifs函数。

目的：计算销往“北京”地区且销量大于30的男销售员销量之和。

方法：

1、在目标单元格中输入公式：=SUMIFS(C3:C9,C3:C9,">30",D3:D9,"男",E3:E9,"北京")。

解读：

1、Sumifs函数为多条件求和函数。

2、语法结构：=Sumifs(求和范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。

三、多条件查找引用函数——Lookup函数。

目的：查询销售员在相应地区的销量。

方法：

1、在目标单元格中输入公式：=LOOKUP(1,0/(($B$3:$B$9=$H$3)*($E$3:$E$9=$I$3)),C3:C9)。

解读：

1、此用法为Lookup函数的变异用法。首先比较$B$3:$B$9=$H$3是否成绩，形成一个数组，然后比较$E$3:$E$9=$I$3是否成立，形成一个数组，最后两个数组相乘形成一个新的数组，条件同时成立的返回1，否则返回0。0除以1返回0,0除以0返回错误，查找值1和运算结果比较，之后0相近，所以返回0所在行的值。

2、语法结构：=Lookup(1,0/((条件范围1=条件1）*（条件范围2=条件2)……（条件范围N=条件N)),返回值范围)。

四、一对多查询实用技巧——Index+Small+If+Row函数的组合应用。

目的：查询销售员销往的地区。

方法：

1、在目标单元格中输入公式：=INDEX(E:E,SMALL(IF(B$3:B$9=H$3,ROW($3:$9),4^8),ROW(A1)))&""。

2、Ctrl+Shift+Enter填充。

3、拖动填充柄填充。

解读：

1、首先判断B$3:B$9=H$3是否成立，返回一个由True和False构成的数组，然后由ROW($3:$9)返回3,4,5,6,7,8,9，然后由IF函数判断，如果B$3:B$9=H$3成立，则返回对应位置上的ROW($3:$9)，否则返回4^8。

2、用Small函数返回第一个最小的值，以此类推。

3、用Index返回E列上对应位置的值。

4、目前Excel中最大的行数为4^8行，所以在此处用4^8进行辅助运算。

结束语：

上文中的4个万能函数，对于计数、求和、查询以及一对多查询进行了演示和系统的分析，希望对大家今后的工作有所帮助。

学习过程中遇到困难可以在留言区留言讨论哦，同时欢迎大家交流学习经验和心得！

Excel中，函数公式的威力强大是众所周知的，但是，好多人还是应用不好，今天，我们对常用的万能公式，做一下讲解，希望对大家的工作有所帮助。

一、多条件计数统计——Countifs函数。

目的：计算销往“北京”地区且销量大于30的男销售员人数。

方法：

1、在目标单元格中输入公式：=COUNTIFS(C3:C9,">30",D3:D9,"男",E3:E9,"北京")。

解读：

1、Countifs函数为多条件计数函数。

2、语法结构：=Countifs(条件1范围，条件1，条件2范围，条件2……条件N范围，条件N）。

二、多条件求和统计——Sumifs函数。

目的：计算销往“北京”地区且销量大于30的男销售员销量之和。

方法：

1、在目标单元格中输入公式：=SUMIFS(C3:C9,C3:C9,">30",D3:D9,"男",E3:E9,"北京")。

解读：

1、Sumifs函数为多条件求和函数。

2、语法结构：=Sumifs(求和范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。

三、多条件查找引用函数——Lookup函数。

目的：查询销售员在相应地区的销量。

方法：

1、在目标单元格中输入公式：=LOOKUP(1,0/(($B$3:$B$9=$H$3)*($E$3:$E$9=$I$3)),C3:C9)。

解读：

1、此用法为Lookup函数的变异用法。首先比较$B$3:$B$9=$H$3是否成绩，形成一个数组，然后比较$E$3:$E$9=$I$3是否成立，形成一个数组，最后两个数组相乘形成一个新的数组，条件同时成立的返回1，否则返回0。0除以1返回0,0除以0返回错误，查找值1和运算结果比较，之后0相近，所以返回0所在行的值。

2、语法结构：=Lookup(1,0/((条件范围1=条件1）*（条件范围2=条件2)……（条件范围N=条件N)),返回值范围)。

四、一对多查询实用技巧——Index+Small+If+Row函数的组合应用。

目的：查询销售员销往的地区。

方法：

1、在目标单元格中输入公式：=INDEX(E:E,SMALL(IF(B$3:B$9=H$3,ROW($3:$9),4^8),ROW(A1)))&""。

2、Ctrl+Shift+Enter填充。

3、拖动填充柄填充。

解读：

1、首先判断B$3:B$9=H$3是否成立，返回一个由True和False构成的数组，然后由ROW($3:$9)返回3,4,5,6,7,8,9，然后由IF函数判断，如果B$3:B$9=H$3成立，则返回对应位置上的ROW($3:$9)，否则返回4^8。

2、用Small函数返回第一个最小的值，以此类推。

3、用Index返回E列上对应位置的值。

4、目前Excel中最大的行数为4^8行，所以在此处用4^8进行辅助运算。

结束语：

上文中的4个万能函数，对于计数、求和、查询以及一对多查询进行了演示和系统的分析，希望对大家今后的工作有所帮助。

学习过程中遇到困难可以在留言区留言讨论哦，同时欢迎大家交流学习经验和心得！

VLOOKUP函数在excel中应用广泛，查找数据很方便，在使用Vlookup时，用于匹配的数据必须是唯一的，可是如果碰到一个目标对应好几个值该怎么办呢？

今天就来讲一下如何用VLOOKUP函数进行一对多查找；

我们知道VLOOKUP函数的基本用法，如下图，这个基本用法咱们在前几天讲过；

来看今天的主题，一对多查找；下图：一个业务经理管好几个业务员，根据业务经理姓名，怎么查找对应的业务员们；

首先，在前面加一列辅助列

圩

为什么要在前面加一列呢，因为VLOOKUP函数的查找方式就是从前往后查找，有的人说我可以在后面加，然后再用公式把数据区域调换，当然也可以哈，如果不嫌麻烦的话。我们的例子就看在前面加辅助列的方式；

在A2单元格输入=B2&COUNTIF($B$2:B2,B2)，下拉填充；

释义：COUNTIF函数是条件计数的一个函数，写法是：COUNTIF(条件所在区域，条件)，得出结果为一个数值；

$B$2:B2，往下拖到B3单元格就变成了$B$2:B3，拖到B18单元格就变成了$B$2:B18，后面的条件B2同理；

然后，在F2单元格输入=IFERROR(VLOOKUP($E$2&ROW(A1),A:C,3,0),"")，下拉填充；

E2是业务经理姓名(鲁长风)，ROW(A1)表示单元格A1所在的行，与“鲁长风”连接就是“鲁长风1”，公式下拉到F2，就是“鲁长风2”，正好做为VLOOKUP函数的第一个参数，类推；

再把E2单元格做一个下拉菜单，选择业务经理姓名，对应的业务员姓名就会产生了；

IFERROR函数：判断正确性，如果正确，就显示正确结果，如果错误，则根据需要显示成规定条件，=IFERROR(C2/D2,"")公式中，如果正确就显示C2/D2的结果，错误就显示为空；

转载自百家号作者：Excel函数公式

实际工作中，一个人往往含有多种角色，也可能干多种类型的工作，如何快速的查找对应的全部角色或全部工作的类型，是每位办公白领必备的技能之一。

一、INDEX+SMALL+IF+ROW组合函数法。

目的：查询销售员的销售情况。

实际效果：

从最终的效果图中我们可以看出，单击销售员的姓名，系统自动检索销售地区和销售额。而且当有多条记录时，“地区”和“销售额”全部予以显示。一瞬间得到了该销售员的销售业绩情况。那么，该如何去实现呢？

步骤：

（一）、高亮度显示该销售员的销售记录。

方法：

1、选中目标单元格。

2、【开始】-【条件格式】-【新建规则】，选中【选择规则类型】中的【使用公式确定要设置格式的单元格】，并在【为符合此公式的值设置单元格格式】中输入：=($H$4=$B3)。

3、单击右下角【格式】-【填充】，选取填充色，并【确定】-【确定】完成设置。

4、单击销售员姓名下拉列表，可以看到关于该销售员的所有销售记录都被高亮度显示。

（二）、查询销售员对应的销售地区。

方法：

1、在目标单元格中输入公式：=INDEX(E:E,SMALL(IF(B$3:B$9=H$4,ROW($3:$9),4^8),ROW(A1)))&""。

2、Ctrl+Shift+Enter填充。

3、拖动填充柄填充。

（三）、查询销售员在对应地区的销售额。

方法：

1、选中目标单元格。

2、输入公式：=IFERROR(LOOKUP(1,0/(($B$3:$B$9=$H$4)*($E$3:$E$9=$I4)),$C$3:$C$9),"")。

3、Ctrl+Enter填充。

二、COUNTIF+VLOOKUP+ROW组合函数法。

目的：查询销售员的销售情况。

实际效果：

眼尖的同学们肯定已经注意到了，最终的效果和用INDEX函数等实现的是一样的，但是数据源中多了一个“辅助列”。那么辅助列有什么作用呢？整体又该如何去实现呢？

步骤：

（一）、添加辅助列。

方法：

1、单击选中【姓名】所在列。

2、快捷键：Ctrl+Shift++添加列，输入列标。

3、选中添加列的所有目标单元格，并输入公式：=COUNTIF($C$3:C3,$I$4)，Ctrl+Enter填充。

备注：

公式：=COUNTIF($C$3:C3,$I$4)的主要作用是对出现的姓名进行累加统计。暨第一次出现为1，第二次出现为2，第三次出现为3……。

（二）、高亮度显示该销售员的销售记录。

方法同上，不在赘述。

（三）、查询销售员对应的销售地区。

方法：

1、选定目标单元格。

2、输入公式：=IFERROR(VLOOKUP(ROW(1:1),$B$3:$F$9,5,0),"")。

3、Ctrl+Enter填充。

（四）、查询销售员在对应地区的销售额。

方法：

1、选中目标单元格。

2、输入公式：=IFERROR(LOOKUP(1,0/(($C$3:$C$9=$I$4)*($F$3:$F$9=$J4)),$D$3:$D$9),"")。

3、Ctrl+Enter填充。

4、数据查询。

结束语：

本文主要讲解了“一对多”查询的两种方法，都是使用相关函数组合使用得来的结果。

如果大家在学习的过程中有困难，建议先学习单个基础函数，然后进行组合使用。

欢迎大家在留言区留言讨论。

上次提到vlookup只能返回一个结果，有些同学要问了，如果想查多个结果呢

比如下面的例子，问题：查找二班都有哪些人？

当然，方法有很多，比如筛选、排序、或者做透视表都可以手动轻松解决，但是想要自动化还是首选函数公式

如果我们用vlookup直接查找二班，那么只会返回出第一条小赵，后面的人都不会显示出来

解决思路 ：vlookup的返回机制是返回第一条查询的结果，就算有很多结果也只返回第一条，那么我们的核心思路就变成了，如果让很多相同的结果，变成不相同，那么我们就选择增加辅助列

在A列增加辅助列，我们的目的是给每一个班级增加一个出现序号，二班第一次出现就是二班1，第二次出现就是二班2，以此类推，COUNTIF($B$1:B2,B2)在第一讲的时候已经提高多，是为了统计到本行为止，出现过几次,这样就得到“班级&序号”的组合。

到了vlookup查询阶段 完整公式=IFERROR(VLOOKUP($H$1&ROW(H1),A:E,3,0),"")

公式中有两个新出现的函数，row()和iferror()，这里简要说明，不做重点介绍。

ROW(单元格)是为了得到序号，比如row(h1)=1,row(h2)=2，得到的就是行号，那么与H1的二班组合成，二班1这个结果，正好与我们之前做的辅助列相吻合。

iferror的作用是，如果出现错误值，则返回空；是为了解决vlookup中如果查询不到结果则会返回#N/A的问题，这个自己多做尝试 完整是iferror(判断内容,返回结果)

在单元格H2输入内容后，向下拖拽到出了空白即可

本图文案例教程 由本人所录制视频转制而成,喜欢看视频的同学请移步到头条号Excel880观看地址如下

http://toutiao/i6419216359292404226/

关键字:

案例文件可到EXCEL880网站下载

详细步骤图文演示如下

案例情况

2.数据情况

3.公式

=""&index($b:$b,small(if($a$1:$a$31=f$2,row($a$1:$a$31),10000),row(a1)))

这样的公式右拉下拉就可以完成 格式转换

详解可以看视频

在Excel中使用函数进行一对多的查找，常常使用网红公式：index+small+if 。

如下面的例子：求类型为法师的所有英雄姓名。

=INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=''法师'',ROW($1:$10),9^9),ROW(A1)))

尽管index+small+if很红很火，但其实效率并不算高。

应用辅助列，不单功能强大，而且效率可观。

辅助列公式：=B2&COUNTIF(B$2:B2,B2)

查找公式：=INDEX($A$2:$A$10,MATCH($E$1&ROW(),$C$2:$C$10,0),)

若需要容错处理，添加iferror函数。

=IFERROR(INDEX($A$2:$A$10,MATCH($E$1&ROW(),$C$2:$C$10,0),),'''')

图片来源于网络

现有学生成绩单一份。

图：原始数据

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

图：预计实现效果

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

图：构造辅助列

在I5单元格输入公式

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

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

图：完成一对多查找