LOOKUP是一个在工作中经常会用到的函数,他是一个查找函数,既可以从上往下竖着查找,也可以从左往右横向查找。
LOOKUP(查找对象,查找区域,结果区域)
第一个参数查找对象,就是要查找的值,可以是数字、文本、逻辑值或引用值的名称或引用;
第二个参数查找区域,就是搜索查找对象的区域,他只能是单行或者单列,查找区域中的值可以是文本、数字或逻辑值。;
第三个参数结果区域,他和查找区域必须大小相同且一一对应。
由此可见,LOOKUP函数不仅可以纵向从左往右查找,可以从右往左查找。还可以横向从上往下查找,或者从下往上查找。
多条件查询的写法为:
=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域)
或者:
=LOOKUP(1,0/(条件区域1=条件1)/(条件区域2=条件2)结果区域)
如图,查询员工A的香蕉的销售额:
=LOOKUP(1,0/((A2:A5=E2)*(B2:B5=F2)),C2:C5)
或者
=LOOKUP(1,0/(( A2:A5=E2)/( B2:B5=F2)),C2:C5)
公式中:
第一个参数1,在这里是TRUE的意思,在查找区域中查找返回的TRUE值;
A2:A5=E2和B2:B5=F2返回结果为TRUE或者FALSE的数组,也就是0或者1;
当除数为0时没有意义,会返回错误值,所以0除以数组中的TURE(1)或者FALSE(0),是也会分别返回TRUE或者错误值;
当查找区域返回TRUE值是,返回结果区域对应的值,错误值则会被LOOKUP自动忽略。
如图,根据销售额在对照表中查找对应的提成率。
E2单元格公式为:= LOOKUP(C2,A9:B14)
在使用近似查找是,对照表的首列必须是升序处理。
单元格A1内容为“58号学生”,在A1中提取学号,则公式为:
= -LOOKUP(0,-LEFT(A1,ROW(1:9)))
ROW(1:9)返回包含1到9的数组,就是{1,2,3,4,5,6,7,8,9}。
LEFT(A1,ROW(1:9))在A1单元格中截取9个数据,分别为5、58及错误值,错误值会自动被LOOKUP忽略。
-LEFT(A1,ROW(1:9)则分别为-5和-58;
LOOKUP在{-5; -58}中找0。0比这组数据都大,当查找值大于查找范围中所有数据时,LOOKUP的实质就是在找最后一个数据。所以返回-58,;
再再LOOKUP前增加负号计算,返回58。
如果是在右边截取数值,则把LEFT换为RIGHT函数,即可。
单元格A1内容为“学号是58”,在A1中提取学号,则公式为:
= -LOOKUP(0,-RIGHT(A1,ROW(1:9))),原理与从左边截取相同。
单元格A1内容为“学号是58的学生”,在A1中提取学号,则公式为:
=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1) CTRL+SHIFT+ENTER三件退出。
公式中:
9^9是9的9次方就是一个非常大的数,让lookup返回单元格中最下面的一个值,没有完全匹配的数字时,会自动区配最后一个数值。
MID(A1,ROW(1:9)在A1单元格中分别截取1到9个字符,返回数值{'学';'号';'是';'5';'8';'的';'学';'生';''}
MID(A1,ROW(1:9)^0也就是{'学';'号';'是';'5';'8';'的';'学';'生';''}的0次幂,汉字的0次幂会返回错误值,数值的0次幂返回1,也就是数组:
{#VALUE!;#VALUE!;#VALUE!;1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
MATCH(1, {#VALUE!;#VALUE!;#VALUE!;1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!},0),在数组中查找1.返回4;
MID(A,1,MATCH(1,MID(A2,ROW(1:9),1)^0,0),ROW(1:9))也就是:
MID(A1,4,ROW(1:9))*1,在A单元格中第四位开始截取字符,截取9次,返回数组:{5;58;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},乘1是将返回的字符转换为数值;LOOKUP函数当查到到最后一个数字58时,则返回结果区域对应的单元格内容。
如图,查找苹果的单价。
E3单元格公式为:=LOOKUP(9^9,FIND(D3,A3:A6),B3:B6)
FIND(D3,A3:A6)在A3到D6单元格中查找D3单元格内容,返回一个数值4;
LOOKUP函数会总最小数开始查找,当查到到4时,有其对应的值,则返回结果区域对应的单元格内容。
也可以写为:
=-LOOKUP(0,-FIND(A3:A5,D3),B3:B5)
0是最趋近于最大负数的数字,第二个参数前增加负号,返回负值,LOOKUP函数当查到到-4时,则返回结果区域对应的单元格内容,再前面增加负号计算为正值。
。
如图,查找红富士苹果的单价。
E3单元格公式为:=LOOKUP(9^9,FIND(A3:A5,D3),B3:B5)
全称查简称与简称查全称原理相同。
联系客服