赞
踩
当有缘你看到这篇文章,如果觉得有用,那么就请多转发,喜欢可以关注下,你们的支持就是我创作的动力,谢谢支持。
逆向查询的解决办法有三种:index+match组合,lookup和vlookup,很多人对lookup和vlookup望而却步,有的会套用别人的公式,不知道原理。有的人看到公式一头雾水,没有了学习和研究的欲望,那么今天我们就来详细介绍下这两个函数逆向查询的方式,学会以后,你就会豁然开朗。
lookup函数多条件逆向查询的公式为:lookup(1,0/条件1*条件2...条件N)
vlookup函数多条件逆向查询的公式为:vlookup(查找值,if({1,0},查询列1,查询列2),结果列,匹配方式)
1.使用lookup逆向查询
公式解读:=LOOKUP(1,0/(C3:C7=E3),B3:B7)
最关键的部分是0/(C3:C7=E3)
先从C3:C7拿出第一个单元格C3和E3进行比较,如果说相等返回true,也就是1,如果不相等返回false,也就是0,结果是不相等,所以变为0/0,那么就返回#DIV/0!,也就是被0除错误,以此类推,返回的值应该为#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,excel在内部给它构造成了1列5行的数组,我们按F9可以看到。
可以看到变为了{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!},大家可以看到数组用;分割,在数值行是用;分割,列是用,分割,所以这个是个1列5行的数组,实际上0/(C3:C7=E3),就变成了这样的形式,查询列就是这样。
#DIV/0!
#DIV/0!
0
#DIV/0!
#DIV/0!
错误值不参加计算,在lookup的使用过程中,查询范围里面的值小于或者等于查询值的最大值,就是要查询的内容,因为错误值忽略计算,只有0符合,所以返回结果就是0这个值对应的结果行,所以是A003
2.使用vlookup逆向查询
公式解读:=VLOOKUP(E3,IF({1,0},C3:C7,B3:B7),2,FALSE)
最主要的就是这一部分 IF({1,0},C3:C7,B3:B7)
它可以分解为两部分,if(1,C3:C7,B3:B7),if(0,C3:C7,B3:B7)
if(1,C3:C7,B3:B7) 就返回了C3:C7区域
if(0,C3:C7,B3:B7) 就返回了B3:B7区域
他们就变成了这样的形式,如图:
也就是内部进行了列的互换,这样就变成了正向查询,实际按F9,我们可以看下,内部的形式是个数组:
这样,我们查询姓名的时候,实际就不是逆向查询了,知道了原理以后,是不是觉得很简单呢。实际上vlookup和lookup的多条件查询也是这样的原理,下次我们详细说下。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。