都说VLOOKUP是什么大众情人、使用频率最高的,哼,有了我LOOKUP,他VLOOKUP可以退隐山林了,去慢慢感叹:既生我VLOOKUP,何生他LOOKUP!
函数语法解析
函数LOOKUP有两种语法形式:向量形式和数组形式
向量形式
1、函数定义:
在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。
2、语法格式:
LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP(查找值,查找区域,结果区域)
3、参数说明:
①、lookup_value:必需。LOOKUP在第一个向量中搜索的值。
Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。
②、lookup_vector:必需。 只包含一行或一列的区域。
lookup_vector中的值可以是文本、数字或逻辑值。
③、result_vector:可选。只包含一行或一列的区域。
result_vector参数必须与lookup_vector参数大小相同。其大小必须相同。
4、注意事项:
①、如果LOOKUP函数找不到lookup_value,则该函数会与lookup_vector中小于或等于lookup_value的最大值进行匹配。
②、如果lookup_value小于lookup_vector中的最小值,则LOOKUP会返回#N/A错误值。
③、重要:lookup_vector中的值必须按升序排列:...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE;否则,LOOKUP可能无法返回正确的值。文本不区分大小写。
数组形式
1、函数定义:
在数组的第一行或第一列中查找指定的值,并返回数组最后一行或最后一列中同一位置的值。
当要匹配的值位于数组的第一行或第一列中时,请使用LOOKUP的这种形式。
2、语法格式:
LOOKUP(lookup_value,array)
3、参数说明:
①、lookup_value:必需。LOOKUP在数组中搜索的值。
lookup_value参数可以是数字、文本、逻辑值、名称或对值的引用。
②、array:必需。包含要与lookup_value进行比较的文本、数字或逻辑值的单元格区域。
4、注意事项:
①、如果LOOKUP找不到lookup_value的值,它会使用数组中小于或等于lookup_value的最大值。
②、如果lookup_value的值小于第一行或第一列中的最小值(取决于数组维度),LOOKUP会返回#N/A错误值。
③、LOOKUP的数组形式与HLOOKUP和VLOOKUP函数非常相似。区别在于:HLOOKUP在第一行中搜索lookup_value的值,VLOOKUP在第一列中搜索,而LOOKUP根据数组维度进行搜索。
④、如果数组包含宽度比高度大的区域(列数多于行数)LOOKUP会在第一行中搜索lookup_value的值。
⑤、如果数组是正方的或者高度大于宽度(行数多于列数),LOOKUP会在第一列中进行搜索。
⑥、使用HLOOKUP和VLOOKUP函数,您可以通过索引以向下或遍历的方式搜索,但是LOOKUP始终选择行或列中的最后一个值。
⑦、重要:数组中的值必须按升序排列:...,-2,-1,0,1,2, ...,A-Z,FALSE,TRUE;否则,LOOKUP可能无法返回正确的值。文本不区分大小写。
经典套路(通用公式):
1、LOOKUP(1,0/((条件1)*(条件2)*…*(条件n)),返回区域)
2、LOOKUP(2,1/((条件1)*(条件2)*…*(条件n)),返回区域)
函数示例
▲
01
经典用法
向量形式:
公式:
=LOOKUP(F3,A3:A8,D3:D8)
注意:lookup_vector中的值必须按升序排列。
数组形式:
公式:
=LOOKUP(F3,A3:D8)
注意:数组中的值必须按升序排列。
当要匹配的值位于数组的第一行或第一列中时,可以使用数组形式。
套路:
公式:
=LOOKUP(1,0/(N3=I3:I8),L3:L8)
注意:乱序情况也可以使用。
▲
02
区间查找
向量形式:
公式:
=LOOKUP(B14,E$14:E$17,F$14:F$17)
注意:lookup_vector中的值必须按升序排列。
数组形式:
公式:
=LOOKUP(B14,E$14:F$17)
注意:数组中的值必须按升序排列。
当要匹配的值位于数组的第一行或第一列中时,可以使用数组形式。
▲
03
格式不一致的查找
分两种情况:
第一种:查找值文本型,查找区域数值型
向量形式:
公式:
=LOOKUP(D25*1,A25:A30,B25:B30)
解析:查找值和查找区域格式不一致将出现错误值#N/A
需将查找值转换为和查找区域一样的格式
转换的方式很多种,比如: 0,-0,--,*1,/1,^1......等等。
注意:lookup_vector中的值必须按升序排列。
数组形式:
公式:
=LOOKUP(D25*1,A25:B30)
注意:数组中的值必须按升序排列。
当要匹配的值位于数组的第一行或第一列中时,可以使用数组形式。
套路:
公式:
=LOOKUP(2,1/(G25:G30=J25*1),H25:H30)
注意:乱序情况也可以使用。
第二种:查找值数值型,查找区域文本型
向量形式:
公式:
=LOOKUP(D36&'',A36:A41,B36:B41)
解析:查找值和查找区域格式不一致将出现错误值#N/A
查找值数值型,查找区域文本型,将查找值连接个空(&'')变为文本,格式统一后就能查找出正确结果了。
注意:lookup_vector中的值必须按升序排列。
数组形式:
公式:
=LOOKUP(D36&'',A36:B41)
注意:数组中的值必须按升序排列。
当要匹配的值位于数组的第一行或第一列中时,可以使用数组形式。
套路:
公式:
=LOOKUP(1,0/(G36:G41=J36&''),H36:H41)
注意:乱序情况也可以使用。
▲
04
查找最后一个文本
公式:
=LOOKUP('座',A47:A58)
解析:用“座”或“々”等较大的汉字查找区域中最后一个文本。
▲
05
查找最后一个数字
公式:
=LOOKUP(9E 307,A64:A75)
解析:9E 307是科学记数,表示9*10^307,是Excel允许键入的最大数值,可用来返回最后一个数值。
▲
06
查找最后一次采购单价
公式:
=LOOKUP(1,0/(B81:B92=E81),C81:C92)
通用公式:LOOKUP(1,0/(条件1),返回区域)
解析:
(B81:B92=E81)条件成立返回TRUE,条件不成立返回FALSE;
发生四则运算时TRUE相当于1,FALSE相当于0;
利用0/任何数=0、0/0=#DIV/0!的特性,0/(B81:B92=E81)部分构成了一个由0和#DIV/0!组成的数组;
用大于第二个参数所有数值的1作为查找值,即可查找出最后一次采购单价。
▲
07
通配符查找
公式:
=IFNA(LOOKUP(,0/FIND(D98,A$98:A$102),B$98:B$102),'')
注意:LOOKUP函数不支持通配符使用,可以用LOOKUP FIND组合
函数IFNA起容错作用
有关函数IFNA的用法,请点击下面链接:
▲
08
取消合并单元格并填充
第一种:全部为文本
公式:
=LOOKUP('々',A$109:A109)
解析:可以用“々”或者“座”,不会打“々”的可以按快捷键<Alt 41385>
第二种:全部为数字
公式:
=LOOKUP(9E 307,F$109:F109)
第三种:文本数字混合
公式:
=LOOKUP(1,0/($I$109:I109<>''),$I$109:I109)
▲
09
查找返回多列数据
公式:
=LOOKUP(,0/($F131=$A131:$A136),B131:B136),向右填充
▲
10
反向查找
公式:
=LOOKUP(,0/($F142=$D142:$D147),A142:A147),向右填充
▲
11
交叉查询
公式:
=LOOKUP(,0/(A153:A158=F153),OFFSET(A153:A158,,MATCH(G153,B152:D152,0)))
解析:MATCH(G153,B152:D152,0)部分找到5月在区域B152:D152中的位置为2;
OFFSET部分是以区域A153:A158为基点,偏移0行2列,返回新区域C153:C158的引用;
那么公式就是LOOKUP(,0/(A153:A158=F153),C153:C158)
不这样写是为了实现动态查询,当业务员或月份更改时,其对应数据区域自动改变,实现自动化查询。
▲
12
合并单元格的查询问题
公式:
=LOOKUP('座',OFFSET(A164,,,MATCH(G164,B164:B180,)))
解析:MATCH(G164,B164:B180,)部分找到客服“君柳”在区域B164:B180中的位置为8;
OFFSET(基点,偏移行数,偏移列数,行高,列宽)
OFFSET(A164,,,MATCH(G164,B164:B180,))是以A164单元格为基点,偏移0行0列,返回行高为8的新区域A164:A171的引用。
抹黑按F9得到:
用“座”或“々”等较大的汉字查找区域中最后一个文本。
▲
13
合并单元格的查询问题
公式:
=LOOKUP(,0/(LOOKUP('座',A$186:A186)=G$186:G$192),H$186:H$192)
▲
14
提取不重复项
公式:
=LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$208:A$216,E$207:E207,))-1),A$208:A208)&''
▲
15
提取数字
普通公式:
=LOOKUP(9E 307,--MID(A222,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A222&1234567890)),ROW($1:$99)))
数组公式:=LOOKUP(9^99,--MID(A222,MATCH(,MID(A222,ROW($1:$99),1)*0,),ROW($1:$99))),按<Ctrl Shift Enter>三键结束。
▲
16
按指定次数重复
公式:
=LOOKUP(,0/FREQUENCY(ROW(A1),SUBTOTAL(9,OFFSET(B$232,,,ROW($1:$9)))),A$232:A$236)&''
▲
17
计算合计金额
公式:
=SUM(LOOKUP(A247:A254,D247:E254)*B247:B254)
数组公式,按<Ctrl Shift Enter>三键结束。
注意:数组中的值必须按升序排列,即本题中D列品名升序排列。
▲
18
多条件查找
公式:
=LOOKUP(,0/(E262&F262=A$262:A$269&B$262:B$269),C$262:C$269)
或者公式:
=LOOKUP(,0/((A$262:A$269=E262)*(B$262:B$269=F262)),C$262:C$269)
通用公式:
LOOKUP(1,0/((条件1)*(条件2)*…*(条件n)),返回区域)
▲
19
一对多查找
公式:
=IFERROR(LOOKUP(,0/(D$275&ROW(A1)=A$275:A$283&COUNTIF(INDIRECT('A271:A'&ROW($275:$283)),D$275)),B$275:B$283),'')
数组公式,按<Ctrl Shift Enter>三键结束。
不知道何为二分法的就别说你会LOOKUP,要么你就懂得使用套路!
光说不练假把式,动手操作才是硬道理!
作者:仰望~星空
联系客服