查找函数最经典的公式

Lookup函数是最好的Excel查找函数,尤其是Lookup (1,0/)模式横扫无数查找问题,如反向查找、多条件查找、查找最后一个等。但是绝大多数用户只会应用,不懂原理。

边肖搜索了百度,没有找到一个可以理解的教程,所以边肖不得不写一个详细的教程。

首先,看看lookup函数的一个查找特性:

给定一个足够大的数字(大于该范围内的所有数字),必须返回最后一个值。(原理是二分搜索法,此处不详述)

无论添加什么类型的值,只要小于给定值,都会返回最后一个。

如果有错误值,就忽略它。

忽略错误值的这个特性是很重要的,因为按条件搜索的时候,你只需要把所有不符合条件的都变成错误值,然后就可以用lookup找到最后一个符合条件的。

不合格的怎么变成错误的值?任何被0除的数都会变成错误值#Div/0!这里有一个例子。根据所需名称找到对应的薪资。

E2公式:

=LOOKUP(1,0/(A2:A8=D2),B2:B8)

A2:A8=D2是对A列和D2的名字进行比较,只有两个结果:相同的返回True,不同的返回False。按F9查看。

四则运算中,真等于数1,假等于数0,所以不合格值变成错误值,合格值变成0。

根据lookup忽略错误值,使用足够大的数找到最后一个数的原理,可以按时找到匹配符号的值。

=LOOKUP(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!},B2:B8)

公式中为什么要用1和0?其他数字可以吗?当然,只要第一个数字大于第二个数字。

比如:

=LOOKUP(9999999,99999998/(A2:A8 = D2),B2:B8)

使用1和0看起来很简单,现在你明白了。

所以利用这个原理,也可以实现多列判断,只是增加了对比条件。

=LOOKUP(5,3/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

边肖说:每次分享查找函数公式的时候,总会有人问1,0是什么意思。你现在明白了吗?你身边肯定有99%的同事不明白,所以帮边肖分享一下这篇文章吧。