|
跟李锐学Excel， 高效工作，快乐生活。 我才是Excel的查找引用之王，甩Vlookup几条街！ 今天我要讲的这个函数，80%以上的白领还不会用，但这个函数的含金量很高，建议看到一半看不懂的同学可以先收藏，以免以后想找时找不到！ 前面教程中介绍的VLOOKUP函数的功能就已经很给力了，但威力十分强大的查找引用函数LOOKUP却很少有人会用，其实LOOKUP函数比VLOOKUP函数强大不止10倍！ 凡工作中涉及到查找引用的问题，都可以用LOOKUP函数来解决。就连一些逆向查找，涉及合并单元格的查找，LOOKUP函数也能发挥出意想不到的强大作用，无疑属于职场办公必备函数。 为了让大家认识LOOKUP函数那些不为人知的强大功能，本文贴合办公实际场景，整理了多种LOOKUP函数的应用方法。 01 LOOKUP函数语法解析及基础用法 LOOKUP 函数是 Excel 中威力十分强大的查找引用函数，当需要查询一行或一列并查找另一行或列中的相同位置的值时，就要用到这个函数了。 LOOKUP 函数有两种使用方式：向量形式和数组形式。 第一种形式：向量形式LOOKUP的向量形式在单行区域或单列区域中查找值，然后返回第二个单行区域或单列区域中相同位置的值。 语法 LOOKUP(lookup_value,lookup_vector, [result_vector]) LOOKUP函数向量形式语法具有以下参数： lookup_value必需。 LOOKUP在第一个向量中搜索的值。 Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。 lookup_vector必需。 只包含一行或一列的区域。 lookup_vector中的值可以是文本、数字或逻辑值。 重要: lookup_vector 中的值必须按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否则，LOOKUP可能无法返回正确的值。 文本不区分大小写。 result_vector可选。只包含一行或一列的区域。result_vector参数必须与 lookup_vector参数大小相同。其大小必须相同。 备注 如果 LOOKUP函数找不到 lookup_value，则该函数会与lookup_vector中小于或等于lookup_value的最大值进行匹配。 如果 lookup_value小于 lookup_vector中的最小值，则 LOOKUP会返回 #N/A 错误值。 下面结合一个实际案例讲解LOOKUP函数的向量形式用法。 下图左侧是数据源，需要在右侧的黄色区域输入公式，根据员工编号调用对应的销售额。 在F2输入以下公式，并向下填充。 =LOOKUP(E2,$A$2:$A$12,$C$2:$C$12) 第二种形式：数组形式LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列中同一位置的值。当要匹配的值位于数组的第一行或第一列中时，请使用LOOKUP的这种形式。 语法 LOOKUP(lookup_value,array) LOOKUP函数数组形式语法具有以下参数： lookup_value必需。 LOOKUP在数组中搜索的值。 lookup_value参数可以是数字、文本、逻辑值、名称或对值的引用。 如果 LOOKUP找不到 lookup_value的值，它会使用数组中小于或等于 lookup_value的最大值。 如果 lookup_value的值小于第一行或第一列中的最小值（取决于数组维度），LOOKUP会返回 #N/A 错误值。 Array必需。 包含要与 lookup_value 进行比较的文本、数字或逻辑值的单元格区域。 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可能无法返回正确的值。 文本不区分大小写。 下面结合一个实际案例讲解LOOKUP函数的数组形式用法。 下图左侧是数据源，需要在右侧的黄色区域输入公式，根据员工编号调用对应的销售额。 在F2输入以下公式，并向下填充。 =LOOKUP(E2,$A$2:$C$12) （更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取） 02 LOOKUP函数单条件查找 在LOOKUP函数的基础用法中，单条件查找的前提是数据源按升序排列，但实际工作中很多数据员都是乱序排列的。 这种情况下，如何使用LOOKUP函数来实现查找引用功能呢？ 下面结合一个实际案例，介绍LOOKUP函数单条件查找的用法。 上图中，左侧是数据源区域，右侧是要输入公式进行查找调用的区域。其中黄色单元格的位置是要输入公式的单元格。 其中的尺码为乱序排列，需要根据尺码调用对应的价格。 F2输入以下公式，并向下填充。 =LOOKUP(1,0/($B$2:$B$7=E2),$C$2:$C$7) （更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取） 之前的教程中，我们学习了LOOKUP函数单条件查找的用法，这次来介绍一下LOOKUP函数多条件查找的用法。 还是先来看实际案例，方便帮助大家理解与记忆。 在H2输入以下公式，并将公式向下填充。 =LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$7) （更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取） 介绍完LOOKUP函数单条件查找和多条件查找的方法，下面来介绍LOOKUP函数逆向查找的方法，包括从下向上查找和从右向左查找。 这节教程，我们先来学习从下向上查找，即查找符合条件的最后一个数据。 还是引入一个实际案例，便于大家理解。 上图左侧是数据源区域，右侧是输入公式查找调用的区域，黄色单元格输入公式。 由图可见每个产品的出库都有多个人经办，现在的需求是根据产品查找最后一个经办人。 G2输入以下公式，并将公式向下填充。 =LOOKUP(1,0/($B$2:$B$13=F2),$D$2:$D$13) （更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取） 上一节教程中我们学会了LOOKUP函数从下向上查找的方法，下面来介绍LOOKUP函数从右向左查找的方法。 来看实际案例，便于大家理解。 上图是某企业的出库记录表，其中记录了各个产品的出库明细，包括出库日期，出库产品、数量和经办人，现在需要根据产品查找其对应的最后一次出库的日期。 每个产品对应着多个出库日期，而出库日期又是由远到近升序排列，所以对于某个产品对应的多个出库日期，我们需要提取最后一个，这里要从下向上查找。 同时，产品在B列，要查找的出库日期位于A列，我们不但要从下向上查找，还要从右向左逆向查找。 G2输入以下公式，并将公式向下填充。 =LOOKUP(1,0/($B$2:$B$13=F2),$A$2:$A$13) （更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取） 06 LOOKUP函数多层级区间条件查找 之前的教程，无论是单条件查找、多条件查找，还是从下向上查找、从右向左查找，介绍的都是LOOKUP函数根据条件查找具体结果的案例。 这节教程，咱们学习一个LOOKUP函数根据区间条件进行判断数据所对应的等级的案例。 上图左侧A和B两列是数据源区域，包括姓名和成绩，C列要输入公式，根据成绩来自动判断所处的等级。 判断依据为：
C2输入以下公式，并将公式向下填充。 =LOOKUP(B2,$E$2:$F$5) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 工作中经常会遇到包含合并单元格的表格，很多人对于合并单元格带来的困扰不知如何解决，比如无法直接用函数公式提取数据，无法直接创建数据透视表等。 其实，只要灵活运用一些函数对数据源进行处理和转换，就可以正常使用那些工具来批量操作了。 这节课，咱们就来结合一个实际案例来学习根据合并单元格智能提取对应的数据的方法。 上图中左侧A至C列是数据源区域，需要根据A列的合并单元格，在D列提取对应的组别信息。 D2输入以下公式，并向下填充。 =LOOKUP(1,0/(A$2:A2<>""),A$2:A2) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 利用LOOKUP函数逆向查找的功能，我们可以查找某列最下方的非空数值。 A列数据源中包含数值、文本、空单元格、错误值 在C2单元格输入以下公式 =LOOKUP(9E+307,A:A) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 利用LOOKUP函数逆向查找的功能，我们还可以查找某列最下方的非空文本。 在C2单元格输入以下公式 =LOOKUP("々",A:A) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 10 LOOKUP函数返回本季度首天日期 灵活运用LOOKUP函数，可以提取与日期相关的一些数据，比如返回当前季度的第一天日期。
在A2单元格输入以下公式： =LOOKUP(NOW(),--({1,4,7,10}&"-1")) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 11 LOOKUP函数返回本月首天日期 灵活运用LOOKUP函数，可以提取与日期相关的一些数据，比如返回当前月份的第一天日期。 =LOOKUP(NOW(),--(ROW(1:12)&"-1")) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 12 LOOKUP函数判断日期的上中下旬 灵活运用LOOKUP函数，可以通过自行构建常量数组，实现判断日期上中下寻的需求。
在B2单元格输入以下公式： =LOOKUP(DAY(A2),{1,11,21},{"上旬","中旬","下旬"}) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 13 LOOKUP函数按区域统一查找 工作中有时需要多列数据匹配查找，这时灵活使用LOOKUP函数能达到意想不到的效果。
上图中A列和B列是数据源，需要在黄色单元格输入公式，根据D列和E列的英文调用对应的中文。 选中F2:G7单元格区域，输入以下数组公式，按<Ctrl+Shift+Enter>组合键。 =LOOKUP(D2:E7,A2:B13) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 14 LOOKUP函数提取数值 LOOKUP函数不光是可以查找调用数据，还可以从混杂的字符串中提取数值，这在某种情况下能为我们节省大量的时间和精力，准确性还比手动有保障得多！
上图中A列的数据中既有文本也有数值，而且数值的位置不固定，有的在中间，有的在前面，还有的在后面。 这种数据源导致我们无法通过直接用文本函数截取相应的数值位置，看看LOOKUP函数怎样搞定这个问题吧！ 在B2单元格输入以下数组公式，按按<Ctrl+Shift+Enter>组合键。 =-LOOKUP(0,-MID(A2,MIN(FIND(ROW($1:$9),A2&56^7)),ROW(INDIRECT("1:"&LEN(A2))))) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） 15 LOOKUP函数按关键词归类 在工作中灵活运用LOOKUP函数，还可以处理很多比较复杂的问题。 比如在电商行业中，经常需要将关键词按行业大词归类，这时可以使用LOOKUP函数结合其它函数嵌套实现。
上图中A列是数据源区域，记录了关键词列表，需要我们在黄色区域输入公式，按照D列的行业大词，从关键词中寻找对应行业大词的归属。 归属规则： 1、 要提取关键词中首位出现的行业大词 2、 当关键字中包含多个行业大词时，优先归属为更大的行业大词 3、 数据源中的行业大词顺序是从上到下依次变大 在B2单元格输入以下公式，并将公式向下填充。 =LOOKUP(1,0/FIND("@"&$D$2:$D$13,"@"&A2),$D$2:$D$13) （更详细的公式原理解析和说明，请点击本文底部的“阅读原文”获取） ps. 这只是《史上最全系列》函数教程的其中之一，从vlookup、sum、if、countif、sumif、sumproduct、index、match、datedif、frequency到lookup，每一篇都极耗时间和精力，我力求用最适合的示例，最全的方法、结合更多的场景展现Excel的各种应用技术，方便大家工作中能够根据自己的场景快捷上手，早日实现高效工作，快乐生活！ 在公众号后台回复这些函数的名称，即可获取对应的教程资源。 与此同时，我更要感谢能看到这里的你们，正是你们一如既往的支持，让我能在知识原创的道路上走的更远！衷心祝一直在努力的你们鹏程万里，心想事成！ 如果你觉得有用，就点右上角分享给朋友们看看吧~ 回到公众号首页，点右上角置顶此公众号，不再错过精彩的技巧！ 更多的Excel应用技巧，可以点击文尾的“阅读原文”，获取更多教程。 今天就到这里，大家记得收藏此教程，明天见~ 李 锐 微软全球最有价值专家MVP 新浪微博Excel垂直领域第一签约自媒体 百度名家，百度阅读认证作者 每日分享职场办公技巧原创教程 高效工作，快乐生活！ 微博 @Excel_函数与公式 微信公众号（ExcelLiRui） >>推荐阅读 << （点击蓝字可直接跳转） |
|
|
