3个Excel公式都看不懂！”-英雄云拓展知识分享

最近收到某位学员的问题咨询，问题是如何针对单据编号和物料长代码返回对应的含税数额。以下表：

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

下面经过一个实例跟大家分享一下经常使用的几种多条件查询方法。下表是某电商公司的客户投诉表，现在需要经过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，在4则运算中用1表示。如果不相等则返回FALSE，使用0表示。那末(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部份运算的结果就只有0或1两类情况，由于只有0*1、1*1、1*0这3种情况。用0来除以0和1，由于分母不能为0，所以0/0返回的是毛病，0/1返回的结果为0。Lookup函数在查找的时候是疏忽毛病的，所以只有数据运算结果为1的公式满足条件。

那末我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目标就是将正确结果用0表示，其他的变成毛病值，利用函数查找疏忽毛病这个特点完成查找。总结：本函数由于使用了2分法原理查找，所以如果数据量较大时运算会很慢。

2、VLOOKUP函数

使用G2单元格在A列中查找，如果查找到对应单元格则返回A列向右第2列的数据。简而言之：=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：所有数组公式完成输入后要使用数组3键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小于、⑴大于，通常情况下都是精确查找。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查找出来顺序作为向下偏移的行数，偏移列数量省略表示不偏移，第3个、第4个参数省略表示只返回一个单元格区域。下面我们来总结一下3种方式的利弊。LOOKUP函数使用进程中运算较慢；VLOOKUP函数使用IF（{0,1}）数组公式，理解上存在一定难度；OFFSET+MATCH函数公式简单，可以作为首选方案。