10个示例让你的VLOOKUP函数应用从入门到精通

1010 个示例让你的个示例让你的 VLOOKUPVLOOKUP 函数应用从函数应用从 入门到精通入门到精通 VLOOKUP 函数是众多的 Excel 用户最喜欢和最常用的函数之一 因此介绍 VLOOKUP 函数使用技巧的文章也特别多 在 ExcelExcel 函数学习函数学习 4 4 VLOOKUPVLOOKUP 函数函数 中 我们学习了 VLOOKUP 函数的语法及应用 在 Excel 公式与函数之美前面的 系列文章中 我们又详细探讨了 VLOOKUP 函数的 4 个参数 熟练掌握 VLOOKUP 函数的使用 是 Excel 必备技能之一 下面我们通过 10 个示 例 进一步巩固 VLOOKUP 函数的使用技能 一键直达一键直达 ExcelExcel 函数学习函数学习 4 4 VLOOKUPVLOOKUP 函数函数 一键直达一键直达 ExcelExcel 公式与函数之美公式与函数之美 1515 VLOOKUPVLOOKUP 函数的秘密函数的秘密 一键直达一键直达 ExcelExcel 公式与函数之美公式与函数之美 1919 理解 理解 VLOOKUPVLOOKUP 函数第函数第 4 4 个参数的真实含个参数的真实含 义义 一键直达一键直达 ExcelExcel 公式与函数之美公式与函数之美 2020 MATCHMATCH 函数使函数使 VLOOKUPVLOOKUP 函数更灵活高效函数更灵活高效 一键直达一键直达 ExcelExcel 公式与函数之美公式与函数之美 2121 INDIRECTINDIRECT 函数使函数使 VLOOKUPVLOOKUP 函数实现多函数实现多 表查询表查询 一键直达一键直达 Excel Excel 公式与函数之美公式与函数之美 2222 VLOOKUPVLOOKUP 函数查找技巧函数查找技巧 概述概述 VLOOKUP 函数最擅长在列中查找相匹配的数据 若找到匹配的数据 则在找到 的数据所在行的右边从指定的列中获取数据 示例示例 1 1 查找郭靖的数学成绩 查找郭靖的数学成绩 如图 1 所示 在最左边的列中是学生的姓名 在列 B 至列 E 中是不同科目的成 绩 图 1 现在 我需要从上面的数据中找到郭靖的数学成绩 公式为 VLOOKUP VLOOKUP 郭靖郭靖 A 3 E 10 2 0 A 3 E 10 2 0 公式有 4 个参数 郭靖郭靖 要查找的值 A 3 E 10 A 3 E 10 查找的单元格区域 注意 Excel 在最左列搜索要查找 的值 本例中在 A3 A10 中查找姓名郭靖 2 2 一旦找到了郭靖 将定位到区域的第 2 列 返回郭靖所在行相同行 的值 数值 2 指定从区域中的第 2 列查找成绩 0 0 告诉 VLOOKUP 函数仅查找完全匹配的值 以上面的示例来演示 VLOOKUP 函数是如何工作的 首先 在区域的最左列查找郭靖 从顶部到底部查找并发现在单元格 A7 中存储 着这个值 图 2 一旦找到该值 就会到右边第 2 列 获取其中的值 图 3 可以使用相同结构的公式来获取任意学生任一科目的成绩 例如 查找杨康的化学成绩 公式为 VLOOKUP VLOOKUP 杨康杨康 A 3 E 10 4 0 A 3 E 10 4 0 图 4 在上面的示例中 查找值 学生姓名 在公式中是包含在引号中的 也可以使 用包含查找值的单元格引用 使用单元格引用可以创建动态公式 例如 如果在某单元格中放置要查找的学生姓名 使用公式来查找该学生的数 学成绩 那么当修改学生姓名时 查找的结果将自动更新 图 5 如果在最左边的列中没有找到查找值 那么返回错误值 N A 示例示例 2 2 双向查找 双向查找 在示例 1 中 列数值采用了 硬编码 使用 2 作为列索引值 因此公式总是 返回数学成绩 如果想要查找值和列索引值都是动态的 如下图 6 所示 修改学生姓名或者科 目时 VLOOKUP 函数获取相应的成绩 图 6 要创建双向查找公式 需要使列也是动态的 这样 当用户修改科目时 公式 自动获取正确的列 例如数学是第 2 列 物理是第 3 列 此时 需要使用 MATCH 函数作为列参数 公式为 VLOOKUP A14 A 3 E 10 MATCH B13 A 2 E 2 0 0 VLOOKUP A14 A 3 E 10 MATCH B13 A 2 E 2 0 0 公式中使用 MATCH B13 A 2 E 2 0 MATCH B13 A 2 E 2 0 作为列的数值 MATCH 函数接受科目作为 查找值 单元格 B13 返回该值在 A2 E2 中的位置 因此 如果查找数学 则返回 2 示例示例 3 3 使用下拉列表作为查找值 使用下拉列表作为查找值 在上面的示例中 我们手工输入数据 耗时且易出错 特别是有许多查找值时 一种好的方法是创建查找值列表 然后只需从列表中选择即可 图 7 在单元格 B14 中的公式仍然为 VLOOKUP A14 A 3 E 10 MATCH B13 A 2 E 2 0 0 VLOOKUP A14 A 3 E 10 MATCH B13 A 2 E 2 0 0 查找值在下拉列表中 这些下拉列表是使用 Excel 的数据有效性功能创建的 选择单元格 A14 单击 数据 数据有效性 在 数据有效性 对话框中 设置为 序列 来源选择单元格区域 A3 A10 同样的方法设置单元格 B13 的 下拉列表 示例示例 4 4 三向查找 三向查找 在示例 2 中 使用了一个包含不同学科学生成绩的查找表 是一个使用两个变 量 学生姓名和学科名称 双向查找学生成绩的示例 现在 假设一年中 学生有三种不同的测试 单元测试 期中测试和期末测试 那么 三向查找就是从指定测试中获取学生指定科目的成绩 如下图 8 所示 图 8 在图 8 的示例中 VLOOKUP 函数可以查找三个不同的表 单元测试 期中测试 和期末测试 返回其中某学生的某学科的成绩 在单元格 H4 中的公式为 VLOOKUP G4 CHOOSE IF H2 VLOOKUP G4 CHOOSE IF H2 单元测试单元测试 1 IF H2 1 IF H2 期中测试期中测试 2 3 2 3 A 3 E 7 A 11 E 15 A 19 E 23 MATCH H3 A 2 E 2 0 0 A 3 E 7 A 11 E 15 A 19 E 23 MATCH H3 A 2 E 2 0 0 公式使用 CHOOSE 函数来确定要引用的表 公式中的 CHOOSE 函数为 CHOOSE IF H2 CHOOSE IF H2 单元测试单元测试 1 IF H2 1 IF H2 期中测试期中测试 2 3 2 3 A 3 E 7 A 11 E 15 A 19 E 23 A 3 E 7 A 11 E 15 A 19 E 23 第 1 个参数是 IF H2 IF H2 单元测试单元测试 1 IF H2 1 IF H2 期中测试期中测试 2 3 2 3 检查单元格 H2 中的值 返回要选择各类测试表所对应的数值 如果是 单元测试 则返回 1 CHOOSE 函数返回单元格区域 A 3 E 7 A 3 E 7 如果是 期中测试 则返回 2 否则返回 3 分别对应着单元格区域 A 11 E 15 A 11 E 15 和 A 19 E 23 A 19 E 23 示例示例 5 5 获取位于列表最后的值 获取位于列表最后的值 可以创建 VLOOKUP 公式来获取位于列表最后一个位置的数字值 在 Excel 中可以使用的最大的正数是 9 99999999999999E 307 这意味着在 VLOOKUP 函数中最大的查找数也是这个数 几乎不会涉及到如此大的一个数的 计算 但可以使用来获取列表中最后一个数字 如图 9 所示 在单元格区域 A1 A14 中有一组数 想要获取列表中最后一个数 即 1514 图 9 公式为 VLOOKUP 9 99999999999999E 307 A 1 A 14 1 TRUE VLOOKUP 9 99999999999999E 307 A 1 A 14 1 TRUE 注意到 公式使用了近似匹配 并且列表也没有排序 下面是使用了近似匹配的 VLOOKUP 函数的工作原理 VLOOKUP 函数从顶到底搜 索最左侧的列 如果发现一个精确匹配的值 则返回该值 如果发现一个高于查找值的值 则返回该值所在单元格上方单元格中的 值 如果查找值大于列表中所有的值 则返回最后一个值 由于 9 99999999999999E