EXCEL中lookup函数的经典查找方式

主讲老师 卢子老师 讲座主题 解析 lookup 函数的经典查找方式 学习是需要技巧和经验的 感谢卢子老师为大家分享和交流他的实战经验 下面是本 期讲座的全部内容 本期讲座包括两部分内容 第一 lookup 函数用法介绍 第二 通过实例讲解 lookup 函数经典的条件查找解法 通用公式基本可以写为 LOOKUP 2 1 条件 查找数组或区域 或 LOOKUP 1 0 条件 查找数组或区域 第一部分 lookup 函数用法介绍 lookup 函数和 vlookup 函数是 excel 中最常用的两个查找函数 vlookup 函数能做到 的 lookup 函数同样可以做到 而且可以做得更好 LOOKUP 函数有两种语法形式 向量和数组 本期就向量形式的展开交流和探讨 向量形式的语法为 LOOKUP lookup value lookup vector result vector 其中的参数意义如下 Lookup value 为所要查找的数值 Lookup value 可以为数字 文本 逻辑值或包含 数值的名称或引用 Lookup vector 为只包含一行或一列的区域 Lookup vector 的数值可以为文本 数 字或逻辑值 Lookup vector 的数值必须按升序排序 2 1 0 1 2 A Z FALSE TRUE 否则 LOOKUP 不能返回正确的结果 文本不区分大小写 Result vector 只包含一行或一列的区域 其大小必须与 lookup vector 相同 比如 lookup A1 B1 B10 C2 C11 其中 C2 C11 的尺寸要与 B1 B10 相同 且如果 A1 对 应 B 列中的位置是 B2 的话 那么返回的将是 C3 的值 LOOKUP 函数说明 第一 如果函数 LOOKUP 找不到 lookup value 则查找 lookup vector 中小于或等 于 lookup value 的最大数值 这就是为何返回最后一个满足条件的值的原理 第二 如果 lookup value 小于 lookup vector 中的最小值 函数 LOOKUP 返回错误 值 N A 利用这个特性 我们可以用 LOOKUP 1 0 条件 引用区域 这样一个通用公式来作查 找引用 第二部分 lookup 函数实例运用 运用一 模糊查找 模糊查找的核心是第二个参数排序必须是升序 否则会导致查找值错误 下图所示的 表 1 是按升序排序的 表 2 没有排序 分别在表 1 和表 2 下面对应的单元格输入公式 表 1 的数据源是按升序排序的 根据 lookup 函数用法 LOOKUP 要查找的数据 查找 范围 结果 在 C24 单元格设置公式 LOOKUP B24 B 5 B 17 C 5 C 17 然后下拉 得到正确结果 表 2 的数据源是没有排序的 在 J24 单元格输入公式 LOOKUP I24 I 5 I 17 J 5 J 17 然后下拉 发现 J25 单元格得到的结果是 H126 显然不对 通过表 2 的源数据可以看到 I25 单元格对应的值应该为 J8 单元格的值 H142 为什么会出错呢 这就印证了第一部分的用法介绍中所讲到的 Lookup vector 的数值 必须按升序排序 2 1 0 1 2 A Z FALSE TRUE 否则 LOOKUP 不能 返回正确的结果 文本不区分大小写 模糊查找 数据源一定要以升序先进行排序 否则就会出错 在数据源没有排序的情 况下 如何才能查找到正确结果 LOOKUP 函数有一个经典的条件查找解法 可以很好的解 决此问题 在第一部分有提到 通用公式基本可以写为 LOOKUP 2 1 条件 查找数组或区域 或 LOOKUP 1 0 条件 查找数组或区域 公式中的 2 1 0 等数字的含义是什么 首先 条件是一组逻辑判断的值或逻辑运算 得到的由 TRUE 和 FALSE 组成或者 0 与非 0 组成的数组 因而 0 条件 的作用是用于构建 一个由 0 或者 DIV 0 错误组成的值 比如数据源中能查找到对应值就是 ture 没有就是 false 形式如 0 True 0 0 false DIV0 查找到就 0 没有就是错误值 如果 LOOKUP 函数找不到 lookup value 即 1 则它与 lookup vector 中小于或 等于 lookup value 的最大值 即 0 匹配 也就是说 要在一个由 0 和 DIV 0 组成的数组中查找 1 肯定找不到 1 因而将返回 小于或等于 1 的最大值 也就是 0 匹配 用大于 0 的数来查找 0 肯定能查到最后一个满足 条件的 以上的原理 被俗称为 以大欺小法 这种技巧在 LOOKUP 函数上的运用是很常见的 利用上面的原理 不管有没有排序 只要使用上面的 以大欺小法 都能得到正确结 果 比如上面实例中 在 J25 单元格输入公式 LOOKUP 1 0 I25 I 5 I 17 J 5 J 17 就可以了 运用二 精确查找 第一 查找的数据没有对应值 可以利用 ISNA ISERROR 函数屏蔽错误值 如上图所示 表 3 是数据源 在下面左边根据 番号 查找 俗称 单击 C51 单元 格 输入公式 LOOKUP 1 0 B51 B 42 B 45 C 42 C 45 然后下拉可以看到下面的 C52 和 C54 单元格出现错误值 这种情况可以利用 ISNA ISERROR 函数屏蔽错误值 只要在公式外面嵌套个 if isna lookup lookup 这样的形式就可以把错误 值屏蔽 在 H51 单元格 输入这样的公式 IF ISNA LOOKUP 1 0 G51 B 42 B 45 C 42 C 45 LOOKUP 1 0 G51 B 42 B 45 C 42 C 45 下拉 就可以屏蔽错 误值了 将错误值屏蔽了 表格就好看多了 上面公式中 是显示空的意思 错误就显示空 没有就查找 第二 借助错误值来判定产品是否存在 下图所示根据左边的数据源 来判定右边对应的数据是否在番号列中 只需要嵌套一个 isna 函数就可以做到 如果没有存在就错误 有存在就 这样的 形式 在 H62 单元格输入公式 IF ISNA LOOKUP 1 0 G62 B 62 B 74 否 是 下拉就即可得出结果 图啥 网友问 iserror 与 isna 函数的区别 ISNA 只屏蔽 N A 错误 ISERROR 屏蔽 所有错误 第三 LOOKUP 函数多条件查找 如上图所示 根据 俗称 和 订单号 来查找 订单数 和 尾数 可以套用这 样的公式 LOOKUP 1 0 条件 1 2 3 引用区域 用 或 将各个条件 连接起来 就是和的意思 此题有两种方法 第一 在 K112 单元格输入公式 LOOKUP 1 0 I112 B 112 B 120 J112 C 112 C 120 D 112 D 120 复制公式就可以得到结果 第二 另外也可以使用这个公式 LOOKUP 1 0 I112 J112 B 112 B 120 C 112 C 120 D 112 D 120 第四 含某个字符查找 按照上图所示 根据左边的数据源 来对含有某个字符进行查找 单击 G128 单元格 输入公式 LOOKUP 1 0 FIND F128 B 128 B 131 B 128 B 131 就可以得到结果 VLOOKUP 函数与 lookup 函数对比 第一 在多条件查找方面 就能看出 lookup 函数好用 用 vlookup 多条件查找 最简 单的方法就是借用辅助列 第二 VLOOKUP 函数对于反向查找是需要嵌套其余函数才能实现 而 LOOKUP 函数没 有正反之分 因此在这方面 LOOKUP 函数会更加容易实现 第三 vlookup 在查找字符方面 可以使用 号类通配符 LOOKUP 是不支持通配符的 但可以使用 FIND 查找字符 数据源区域 的形式代替 本讲座有关的 Excel 源文件请在论坛下载 d 1 讲座主题 VLOOKUP 函数运用 下 在第 3 期的技术讲座中 已经学习了 VLOOKUP 函数的用法 也分析了一些基础例子 无言老师在本期讲座通过几个实例来帮助大家更深入的了解 VLOOKUP 函数的使用 VLOOKUP 函数运用一 VLOOKUP 函数第三个参数返回的列数可以通过 match 函数定位查找的返回所需的列数 vlookup 和 match 嵌合使用 下图所示的 A11 H20 单元格区域是 excel 源数据 match 需要查找的数据表列标题 元数据表列标题范围 0 这是精确查找定位第一 次出现的列位置 请使用 vlookup 函数解出下图所示的 C24 单元格中编号所对应的其余单元格的值 单 击 C24 单元格右下角的向下三角形 可以更换选择其余的编号 在 E24 单元格输入公式 VLOOKUP C 24 A 11 H 20 MATCH D24 A 11 H 11 0 0 即 可得到答案 同样的方法可以求出其余单元格的值 只是把定位的单元格地址更改一下就 好了 VLOOKUP 函数运用二 原工作表存在通配符的查找 如下图所示 原工作表 A 列中存在 通配符 通过上图的源数据 要对下图的 C D E 列对应的数据进行查找 该如何设计公式呢 单击 C231 单元格 输入以下公式 VLOOKUP LEFT B231 2 A 219 G 222 MATCH C 230 A 219 G 219 0 0 然后向右和向下拉即可查找相应出相应的数据 无言老师提到 还可以使用下面这样的公式 也能实现 VLOOKUP MID B239 1 2 A 219 G 222 MATCH C 238 A 219 G 219 0 0 通过这个案例 可以看到 和 的替换作用 号替换的可以为某个文本之前或之后的所 有字符 号代替的只是一个字符 通常来说 通配符用的比较多的还是 号 VLOOKUP 函数运用三 反向查找 VLOOKUP 函数通常只能从左往右的垂直方向有序查找 如果需要用到逆序 反向查找 就需要使用 IF 或 CHOOSE 其中一个函数嵌套使用 这两个函数在 VLOOKUP 函数的使用通常 是这样的形式 IF 1 0 查找内容的列 返回内容的列 和 CHOOSE 1 2 3 查找内容 的列 返回内容的列 1 返回内容的列 2 返回内容的列 3 返回内容的列需要几列就写 入几列 下图所示的是 A258 F288 单元格区域为源数据 为了演示需要 将其中的部分行区域 隐藏了 如下图所示 已知姓名列数据 使用 VLOOKUP 函数查找年龄和工资列的数据 要完成此题 有两种方法可以实现 第一 使用 IF 1 0 嵌套 VLOOKUP 实现 单击年龄下方的 I270 单元格 输入公式 VLOOKUP H270 IF 1 0 B 258 B 288 D 258 D 288 2 0 然后下拉 单击工资下方的 J270 单元格 输入公式 VLOOKUP H270 IF 1 0 B 258 B 288 F 258 F 288 2 0 然后下拉 提示 IF 1 0 函数在这里只能用到 2 个条件 因此使用有一定局限性 下面我们就使用另外一种方法来实现 即借助 CHOOSE 函数 其实 IF 函数可以做到的 CHOOSE 同样能做到 而且 CHOOSE 比 IF 更灵活 第二 CHOOSE 函数和 VLOOKUP 的嵌套使用的公式 同样在年龄下方的 I270 单元格 输入公式 VLOOKUP H288 CHOOSE 1 2 3 B 259 B 288 D 259 D 288 F 259 F 288 2 0 然后下拉 单击工资下方的 J270 单元格 输入公式 VLOOKUP H288 CHOOSE 1 2 3 B 259 B 288 D 259 D 288 F 259 F 288 3 0 如果大家对以上公式不是很理解 可以在公式栏中选中不明白的部分 按下 F9 键 俗 称 抹黑 查看公式对应的执行结果 F9 键在学习函数与公式中 对我们来说 有很大 的帮助作用 帮助我们理解公式 VLOOKUP 函数运用总结 第一 在引用数据区域最好使用绝对引用的方式进行 如果对引用方式不是很清楚的 朋友 可以参看混合引用方式的经典实例 99 乘法表的制作方法 第二 对于引用查找的单元格 格式一定要和查找原表格的数据格式一致 第三 如果是要从右往左查找 必须通过 IF 和 CHOOSE 等函数的配合使用才能实现 本讲座有关的 Excel 源文件请在论坛下载 d 1