“我面试了几十个大学生都不达标，发现他们连这3个excel公式都看不懂！”-凯发k8官网登录vip

关注回复[目录]学习113篇excel教程

之前给大家推送了一篇用pq完成跨表数据核对的教程：《我折腾到半夜，同事用这个excel技巧，30秒跨表核对数据交给领导！》，但由于版本限制，好多伙伴都无法使用，今天给大家介绍公式，同样可以完成数据核对。

最近在微信学习交流群中收到某位学员的问题咨询，问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表：

其实这位学员的问题就是如何实现多条件查询。

下面通过一个实例跟大家分享一下常用的几种多条件查询方法。

下表是某电商公司的客户投诉表，现在需要通过a表中的客户姓名与地区两个条件来查询b表中的产品型号，返回到a表的e列中。

1.lookup函数

函数公式：=lookup(1,0/(a3=$i$3:$i$19)*(b3=$j$3:$j$19),$k$3:$k$19)

公式解析：首先通过a3单元格与b表i列数据做对比，同时用b3单元格与b表j列信息做对比。

在excel中如果两个单元格对比，相等则返回true，在四则运算中用1表示。如果不相等则返回false，使用0表示。

那么(a3=$i$3:$i$19)*(b3=$j$3:$j$19)这部分运算的结果就只有0或者1两种情况，因为只有0*1、1*1、1*0这三种情况。

用0来除以0和1，由于分母不能为0，所以0/0返回的是错误，0/1返回的结果为0。lookup函数在查找的时候是忽略错误的，所以只有数据运算结果为1的公式满足条件。

那么我们就很好理解0/(a3=$i$3:$i$19)*(b3=$j$3:$j$19)的目的就是将正确结果用0表示，其他的变成错误值，利用函数查找忽略错误这个特点完成查找。

总结：本函数由于使用了二分法原理查找，所以如果数据量较大时运算会很慢。

2.vlookup函数

使用g2单元格在a列中查找，如果查找到对应单元格则返回a列向右第二列的数据。简而言之：=vlookup(查找什么，在哪查找，从条件所在列算起找到后返回对应的第几列数据，精确或模糊查找)。

那vlookup如何才能完成多条件查询呢？。

还以客户投诉表为例，按照姓名&地区来匹配产品型号返回到e里中。

其实我们是可以将a、b两表中插入辅助列，将姓名和地区都合并到一个单元格中然后使用vlookup来完成。

但是插入2个辅助列后整个表列数发生变动，在工作中往往单元格中有很多公式，如果列数发生变化将直接导致表格中函数公式运算结果错误。所以添加辅助列的方式虽然简单，但不是最好的方式。

那么不用辅助列如何才能完成多条件查询呢？

首先我们查找值合并很简单，输入函数vlookup时第一个参数可以写成a3&b3，即可将a3、b3两个单元格内容合并，作为查找值。

现在问题查找区域也需要做合并。

如果把两列内容合并在一起，可输入公式=h2:h19&i2:i19，按ctrl shift 回车生成结果，然后下拉公式，这样两个条件就变成了一个。

接下来通过if函数提取对应的j列数据，可输入公式

=if({0,1},h2:h19&i2:i19,j2:j19)，按ctrl shift 回车生成结果，然后下拉公式，{0,1}表示逻辑值{false,true}。

下面我们详细来解析一下:

首先在excel中0表示错误，1以及其他所有数值表示正确。如下表示例：

通过上面的例子我看到如果if判断0则返回错误，判断1则返回正确。

现在我们可以将公式拆分为以下两种情况：

if（0, h2:h19&i2:i19,j2:j19），0表示false，所以只能返回j列数据。

if（1, h2:h19&i2:i19,j2:j19），1表示true，所以只能返回h列和i列合并结果。

那么if({0,1},h2:h19&i2:i19,j2:j19)怎么理解呢？

既然是数组公式，那么可以将它理解为同时返回两组数据，0对应的是j2:j19，1对应的h2:h19&i2:i19，构建了两列数据。

最后我们使用vlookup函数完成嵌套，

=vlookup(a3&b3,if({1,0},h3:h20&i3:i20,j3:j20),2,0)，这里我们就可以理解为用a3&b3在h3:h20&i3:i20中查找对应j3:j20中的数据。因为公式中

if({1,0},h3:h20&i3:i20,j3:j20)返回的顺序是先返回h3:h20&i3:i20再返回j3:j20。

注意：很多人不明白为什么嵌套的时候if第一参数又变成了{1,0}，因为这里我们需要返回的是h和i合并结果作为查找区域。ps：所有数组公式完成输入后要使用数组三键ctrl shift ener来返回运算结果！

这样我们不用辅助列也能通过vlookup函数完成多条件查询。

3.offset match函数

下面举例跟大家分享一下通过offset函数完成多条件查询。

函数公式：

{=offset($j$2,match(a3&b3,$h$3:$h$19&$i$3:$i$19,0),)}

公式解析：

完成多条件查询第一步先要确定a表中姓名&地区合并后对应在b表中姓名&地区的顺序。这里我们通过match来完成，我们用个简单的例子说明。

=match(a2,e:e,0)表示使用a2单元格在e列中查找，0表示精确查找、1小于、-1大于，通常情况下都是精确查找。

match(a3&b3,$h$3:$h$19&$i$3:$i$19,0)表示将a3与b3合并作为查找内容，h列和i列合并作为查找区域，0表示精确查找。

确定顺序后我们通过offset函数以顺序数据作为偏移行数返回对应数值。

offset函数的功能是以指定的单元格引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以为一个单元格或区域。并可以指定返回的行数或列数。reference 作为偏移量参照系的引用区域。reference 必须为对单元格或相连单元格区域的引用；否则，函数 offset 返回错误值#value!。

=offset(j2,1,0,1,1)表示以j2单元格作为参照物向下偏移1行，向右偏移0列，返回1行1列数据区域。

=offset($j$2,match(a3&b3,$h$3:$h$19&$i$3:$i$19,0),)表示以$j$2为参照单元格，通过match查找出来顺序作为向下偏移的行数，偏移列数量省略表示不偏移，第三个、第四个参数省略表示只返回一个单元格区域。

下面我们来总结一下三种方式的利弊。lookup函数使用过程中运算较慢；vlookup函数使用if（{0,1}）数组公式，理解上存在一定难度；offset match函数公式简单，可以作为首选方案。

想要跟随滴答老师全面系统学习excel，不妨关注《一周excel直通车》视频课或者《excel极速贯通班》直播课。

《一周excel直通车》视频课

包含excel技巧、函数公式、

数据透视表、图表。

一次购买，永久学习。

最实用接地气的excel视频课

《一周excel直通车》

风趣易懂，快速高效，带您7天学会excel

38 节视频大课

（已更新完毕，可永久学习）

理论 实操一应俱全

主讲老师： 滴答

excel技术大神，资深..师；

课程粉丝100万 ；

开发有《excel小白脱白系列课》

        《excel极速贯通班》。

原价299元

限时特价 99 元，随时涨价

少喝两杯咖啡，少吃两袋零食

就能习得受用一生的excel职场技能！

  长按下面..立即购买学习

购课后，加客服微信：603830039领取练习课件