Vlookup函数实现多条结果查询

【摘要】

Vlookup函数是大家在工作中最常用也是最为熟悉的查找函数,然而,Vlookup却具备自己的一些“脾气”,比如不管查找对象对应多少个结果,一次只能返回一个并且是第一个结果。

而如果我们需要返回多个结果,通常情况下都是采取Offset、Index函数结合其他函数得到结果,对于大部分用户而言,Offset、Index函数并没有Vlookup函数“亲切熟悉”,并且函数的嵌套以及数组公式的应用往往让大家晕头转向。

本文将给大家介绍如何通过辅助列的方法应用我们熟悉的Vlookup函数查询一个值,返回该值所对应的多个结果,函数并不复杂,重在理解思路的应用。

【正文】

一、需求说明

在如下所示的客户信息表中,每个销售对应有多条客户信息。比如郑建杰。

我在做客户信息查询时,希望当我选择不同销售时,他所对应的所有客户信息都能相应罗列出来。也就是我们所说的多行结果查询。


二、准备:辅助函数的学习

1、单条件计数函数:Countif函数

Countif 是条件计数函数,按照要求的条件,统计符合要求的数据个数。

语法:= Countif(条件范围,条件)

说明:“条件范围”是用来计数的数据范围;“条件”是指明需要的数据的类型或范围。

例如:在以下的销售明细表中统计不同销售人员的订单数量,可以通过以下公式来实现:

=COUNTIF(A:A,G2)


2、返回引用的列号:COLUMN函数

语法: =COLUMN(为需要得到其列标的单元格或单元格区域)

作用:可以得到横向递增的序列。比如在B1单元格输入=column(A1),往右进行公式复制,可以得到1-5的等差序列。



3、返回引用的行号:ROW函数

语法: =ROW(需要得到其行号的单元格或单元格区域)

使用方法与COLUMN函数相同。作用是得到纵向递增的序列。如在B1单元格中输入=ROW(A2),往下进行公式复制,可以得到以2开始的纵向递增的序列。



三、创建辅助列

在客户信息表【客户编号】列前插入一辅助列,输入公式= COUNTIF($H$2:H2,H2)

这个公式返回每个“销售”从上到下是第几次出现,如第二个“郑建杰”标记2。

将A2公式改成:=H2&COUNTIF ($H$2:H2,H2),这样就将H列的“销售姓名”和出现次数连在一起,使得同一个销售的不同记录有了差别。


四、查找函数参数分析

首先了解Vlookup函数精确查找基本参数。

语法:=Vlookup(查询值,数据源,结果在数据源的第几列,0)

我们以郑建杰的结果查询为例分析四个参数的变化情况,如下图所示:



1、查询值:

每一条记录查询值不发生变化,但不同记录查询值序号纵向递增,因此查询值应该是查找对象“销售名字”&“出现次数”。出现次数纵向递增,因此可以用ROW函数实现。

2、数据源:

不管查询哪条记录,查找范围绝对不发生变化,所以数据源为客户信息表:$A:$G。

3、显示序列:

客户编号为数据源中第2列,客户名称第3列,开户行第4列…横向递增,因此,可以通过COLUMN函数。

4、查找类型:

因为所有记录都是精确查询,所以全部为0.

五、查找公式的书写

通过以上每个参数的分析,在K4单元格中输入公式:

=VLOOKUP ($J$4&ROW(A1),$A$1:$G$67,COLUMN(B1),0)

将公式往下往右复制,即可得到所有的结果。



六、完善细节

1、错误值的消除

在进行公式往下复制时,因每个销售所对应的客户记录数目不一致,公式复制到哪一行停止以最高记录数目为准。如客户信息表中郑建杰对应客户信息记录最多,有100条,那么公式则需要复制100行。

但是这样会有一个问题出现,当我查询销售“王伟”对应的客户信息时,因为王伟客户信息只有3条,那么第3行以下的公式则会出现错误值“#N/A”。如下图所示:

此时,可在写好的公式外嵌套一个IFERROR函数进行错误值的消除。

基本语法:IFERROR(value,value_if_error)

Value:指通过IFERROR函数来检查是否存在错误的参数。

Value_if_error:指Value参数计算错误时要返回的值。

以下公式的含义为:对Vlookup返回的结果进行是否是错误值的判断,如果是错误值,则返回空白。一对双引号代表返回空。



2、边框的消除

追求完美的同学会发现,在上图中虽然利用IFERROR函数进行了错误值的消除,但有公式的单元格依旧还存在边框,能否实现如果有内容有边框,没内容则没有边框呢?

可以利用条件格式加以实现。

  1. 首先将所有写好公式的单元格选择,并将所有边框格式进行清除;
  2. 选择好所有公式单元格区域后,【开始】选项卡-【条件格式】-【新建规则】,按如下步骤设置好公式、格式,确定后即可。

更多office技巧可关注微信公众号:黑米粥的世界。期待与你相遇,一起成长~

编辑于 2018-06-13

文章被以下专栏收录