在最新的Office365版中,新增Xlookup函数几乎要完全取代Vlookup。但由于版本的限制,大多数人还只能继续使用Vlookup函数。不过,还有一个函数可以和Xlookup相媲美,它就是大家都知道的
LOOKUP函数
关于Lookup公式兰色以前分享了很多,只是大多数人只会套用,并不理解公式的含义,所以今天兰色想通过更深入的讲解,让同学们真正掌握Lookup函数的用法。
学习Lookup函数,还是要从它的基本语法起步。
lookup函数有两种语法结构,兰色翻译成中文:
Lookup(查找的值,在一列或一行区域中查找,[返回值的一行/列区域])
Lookup(查找的值,数组)
多数同学都看不懂这两种语法的具体用法,兰色还是举例说明一下。
【例】如下图所示,要求根据D2的学号,查找对应的姓名。
公式1:
=LOOKUP(D2,A2:A7,B2:B7)
公式2:
=LOOKUP(D2,A2:B7)
由上面公式可以看出,第2个公式是直接引用2列的单元格区域(A2:B7)。显然比第1种方法更简单。那还学第1种干嘛? 因为有时数据不一定相邻或反向查找需要。如下图所示学号在姓名后面:
既然有Lookup函数,为什么还要设计出VLookup函数? 是因为Lookup有一个致命缺陷。就是它的第2个参数必须按升序排列。微软官方是这么说的:
值必须按升序排列:..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;否则,LOOKUP 可能无法返回正确的值。文本不区分大小写。
如果你不排序,结果可能会出错。(出错原因是lookup是按二分法查找的)
下面的A0004查找的姓名竟然是王五,错!
有可能错误,谁还敢用lookup函数?
想不出错?所以就有了 0/的经典用法:让区域和值进行对比,然后用0除
=LOOKUP(0,0/(A2:A7=D2),B2:B7)
为什么0/就可以?
先选中该局部公式(0/(A2:A7=D2))按F9查看结果,发现除第2个值是0外,其他的全部是错误值#DIV/0!
{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
原理很简单,A2:A7区域中和D2相等返回True,不相等返回False。而在Excel计算时True等同于数字1,而False等同于数字0。当除数是1时,0/1 结果是0,而0/0结果是错误值。
Lookup函数可以忽略错误值,所以只有一个0的数组也无所谓是不是升序了。第1个参数用 0就可以进查找了。
=LOOKUP(0,{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},B2:B7)
用LOOKUP函数好麻烦啊!为什么不用Vlookup?
答案是Lookup可以进行多条件查找,而Vlookup不可以
=LOOKUP(0,0/((A2:A7=A11)*(B2:B7=B11)),C2:C7)
由此,万能的多条件查找公式为:
Lookup(0,0/(条件1*条件2*...条件n),返回值区域)
当然,从右向左查也可以搞定
=LOOKUP(0,0/(B2:B7=D2),A2:A7)
除此之外,当有无法精确查找时,Lookup会从后向前查,查找与被查找值最接近且比它小的值。这个特征让Lookup函数可以实现Vlookup无法完成的任务。
【例】查找A产品最后一次进货价格
=LOOKUP(1,0/(B2:B7=A11),C2:C7)
细心的同学会发现,之前第1个参数是0,现在是用1。原因就是因为有几个符合条件的值,第2个参数就会生成多少0,这时用一个比0大的数字1(也可以是2,3,4.....)就可以从后向前查找,查找到最后一个0值。
【例】提取最前面的数字
=LOOKUP(9^9,--LEFT(B2,ROW(1:10)))
公式说明:
用Left逐个截取(2,23,234,234.34,234.3....),用--(两个负号)转换成数值,文本转换成错误值,最后用一个足够大的数(这里用9^9,也可以是其他更大的数)查找最后一个比它小且最接近的数字。
数字在任意位置?万能提取公式在此:
=LOOKUP(9^9,MID(B2,MATCH(1,MID(B2,ROW(1:9),1)^0,0),ROW(1:9))*1)
最后再分享几个lookup模糊匹配的示例,感受一下它强大的用法。
【例】如下图所示,要求根据提供的城市从上表中查找该市名的第2列的值。
=LOOKUP(9^9,FIND(A7,A2:A4),B2:B4)
【例】如下图所示,要求根据地址从上表中查找所在城市的提成。
=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)
【例】如下图所示的A列,是包括车类别的明细车型,现需要在B列把车类别提取出来。(车类别有四种:捷达,速腾,迈腾,高尔夫)
=LOOKUP(9^9,FIND({'捷达','速腾','迈腾','高尔夫'},A2),{'捷达','速腾','迈腾','高尔夫'})
兰色说:虽然兰色很费力在讲解,估计还是有大多数同学看不太明白这个Excel高手必备函数。所以建议同学们先收藏起来,看不懂的公式就先学会套用,以后慢慢再理解。
联系客服