都说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,要么你就懂得使用套路! 光说不练假把式,动手操作才是硬道理! 作者:仰望~星空 |
|