excel多条件查三种⽅法：lookup、vlookup、indexmatch多
条件查案例
最近在Excel学习交流中收到某位学员的问题咨询，问题是如何返回单据编号和物料长代码对应的含税数额。如下表：
其实这位学员的问题就是excel的多条件查问题。
下⾯通过⼀个实例跟⼤家分享⼀下常⽤的3种excel多条件查函数。
下表是某电商公司的客户投诉表，现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号，返回到A表的E列中。
下⾯是三种excel双条件查返回的⽅法，依次来看：
第⼀ excellookup多条件查询
函数公式：=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这三种情况。
按照⼆分法原理，lookup函数会在⼆分位处查符合条件的数据。⼤家都知道lookup函数想要精准查那么这组数值必须要升序，但实际上这组数据运算结果0和1的顺序是混乱的。
所以就想到了⽤0来除以0和1的⽅式来区分。由于分母不能为0，所以0/0返回的是错误，0/1返回的结果为0。Lookup函数在查的时候是忽略错误的，所以只有数据运算结果为1的公式满⾜条件。
那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的⽬的就是将正确结果⽤0表⽰，其他的变成错误值，利⽤函数查忽略错误这个特点完成查。
总结：本函数由于使⽤了⼆分法原理查，所以如果数据量较⼤时运算会很慢。
第⼆ vlookup多条件查
vlookup函数是我们最常⽤的函数，vlookup函数主要⽤于垂直⽅向上向右查。如下图：
使⽤G2单元格在A列中查，如果查到对应单元格则返回A列向右第⼆列的数据。简⽽⾔之：=VLOOKUP(查什么，在哪查，从条件所在列算起到后返回对应的第⼏列数据，精确或模糊查)。
那vlookup如何才能完成多条件查询呢？。
还以客户投诉表为例，按照姓名&地区来匹配产品型号返回到E⾥中。
其实我们是可以将A、B两表中插⼊辅助列，将姓名和地区都合并到⼀个单元格中然后使⽤vlookup来完成。
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函数完成多条件查询。
第三 OFFSET+MATCH函数公式
很多excel⾼⼿都知道offset可以当vlookup函数使⽤，但职场新⼈⼤多都不了解。
下⾯举例跟⼤家分享⼀下通过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查出来顺序作为向下偏移的⾏数，偏移列数量省略表⽰不偏移，第三个、第四个参数省略表⽰只返回⼀个单元格区域。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。