本帖最后由 boyihuang 于 2016-7-7 20:41 编辑
一般在日常工作应用中,需要查找数据时,VLOOKUP函数/HLOOKUP函数是最常用的。似乎LOOKUP函数用得少一些。但是,相对VLOOKUP/HLOOKUP,LOOKUP函数的自由度更大,在某些条件下表现得更强悍。比如多条件查找数据。
今天就主要讲讲LOOKUP函数的多条件查找在工作中可能的应用。
语法 LOOKUP(lookup_value,lookup_vector,result_vector) LOOKUP(lookup_value,array)
要点 1. LOOKUP函数查找方式是“模糊查找”,没有像VLOOKUP和HLOOKUP那样可以设置精确查找的参数。 2. LOOKUP函数用“二分法”进行查找。 3. 返回小于等于lookup_value(查找值)的最大值。 4. Lookup_vector(查找区域)中如果有“错误值“,那么LOOKUP函数在查找时将会忽略错误值。
先看一个VLOOKUP函数应用的例子:
在绿色单元格输入姓名,B12,C12通过VLOOKUP公式很容易可以查找到相应部门和职务,显然这是单个条件(A12)的查找。
假如我想反过来,输入部门和职务,查找到相应的名字呢?怎么做? 嵌套IF函数当然可以完成,但是多重嵌套IF函数容易出错,效率低。一般我们尽量避免嵌套多重IF函数。没错,用LOOKUP函数。
我来解释一下 =LOOKUP(1,0/((B2:B8=E2)*(C2:C8=F2)),A2:A8) 这个公式。
B2:B8=E2 B2:B8与E2比较,返回的是一个数组,结果只有TURE和FALSE(即1和0)。如果在查找区域找到了“研发部“,那返回的数组里有一个1和6个0。
C2:C8=F2 C2:C8与F2比较,返回的是一个数组,结果只有TURE和FALSE(即1和0)。如果在查找区域找到了“副经理“,那返回的数组里有一个1和6个0。
((B2:B8=E2)*(C2:C8=F2)) 两个数组进行乘法运算,最后得出的也是一个7行一列的数组,同样可能包含1和0。 (关于数组以及数组运算的原理,上一篇文章有详细介绍。)
0/((B2:B8=E2)*(C2:C8=F2)) 0除以任何数(0除外)等于0;但如果被除数为0,则返回错误值。
到此为止,如果在B列和C列都找到符合条件的数据,那么0/((B2:B8=E2)*(C2:C8=F2)) 返回的数组里就一定有一个0值,其它为错误值。见下图:(需要查看某一段公式的运算结果,在公式栏中将其“抹黑”,再按F9键即可。)
LOOKUP(1,0/((B2:B8=E2)*(C2:C8=F2)),A2:A8) 在{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!}里查找1,结果返回A2:A8的对应数据。 PS:请在回头看看上面的“要点“。 0小于1,同时忽略了错误值。0为数组中的第六行数据,所以返回的结果必然是A2:A8中的第六行数据(即A7)。
上面的例子是两个条件查找,多个条件查找也是同样的格式: LOOKUP(1,0/((条件1)*(条件2)*(条件3)*…(条件n)), 结果区域) 这个公式涉及了数组,一旦理解了这个公式,那么在多条件查找的时候将非常好用。
IFERROR(LOOKUP(1,0/((B2:B8=E2)*(C2:C8=F2)),A2:A8),"无记录") 这个公式在原来公式的基础要再套了一个IFERROR函数,目的是万一源数据表里没有相关记录,不至于出现”#N/A!”这种不和谐的符号而已。
当然,LOOKUP函数的用法远不止这一种,利用LOOKUP函数“模糊查找”的属性,还可以是实现对学生成绩的等级判定,销售人员的佣金比例等级判定等等。
掌握LOOKUP函数,那你的手里又多了一个强悍的数据查找工具。
|