Excel VLOOKUP 函数
Excel VLOOKUP 函数是一个强大的工具,它帮助您通过在表格或区域的第一列中垂直匹配来查找指定值,然后从同一行的另一列返回相应的值。虽然 VLOOKUP 非常有用,但对于初学者来说,有时可能难以掌握。本教程旨在通过逐步解释参数、提供有用的示例以及解决您在使用 VLOOKUP 函数时可能遇到的常见错误,帮助您掌握 VLOOKUP。
相关视频
逐步解释参数
如上图所示,VLOOKUP 函数用于根据给定的 ID号码查找邮箱。现在,我将通过逐步分解每个参数,详细解释如何在此示例中使用 VLOOKUP。
步骤1:启动 VLOOKUP 函数
选择一个单元格(此例中为 H6)来输出结果,然后在编辑栏(F)中输入以下内容以启动 VLOOKUP 函数。
=VLOOKUP(
步骤2:指定查找值
首先,在 VLOOKUP 函数中指定查找值(即您要查找的内容)。在此,我引用包含某个 ID号码1005 的单元格 G6。
=VLOOKUP(G6
步骤3:指定表格区域
接下来,指定一个包含您要查找的值和要返回的值的单元格区域。在此例中,我选择区域 B6:E12。公式现在如下所示:
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
步骤4:指定要返回值的列
然后指定要从中返回值的列。
在此示例中,由于我需要根据 ID号码返回邮箱,因此我输入数字4 告诉 VLOOKUP 从数据区域的第四列返回值。
=VLOOKUP(G6,B6:E12,4
步骤5:查找近似匹配或精确匹配
最后,确定您是在寻找近似匹配还是精确匹配。
- 要查找精确匹配,您需要将最后一个参数设置为 FALSE。
- 要查找近似匹配,请将最后一个参数设置为 TRUE,或直接留空。
在此示例中,我使用 FALSE进行精确匹配。公式现在如下所示:
=VLOOKUP(G6,B6:E12,4,FALSE
按 Enter 键获取结果
通过在上述示例中逐一解释每个参数,VLOOKUP 函数的语法和参数现在更容易理解。
语法和参数
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- Lookup_value(必需):您要查找的值(实际值或单元格引用)。请记住,此值必须在表格区域的第一列中。
- Table_array(必需):一个包含查找值列和返回值列的单元格区域。
- Col_index(必需):表示包含返回值的列号的整数。它从表格区域的最左列开始,编号为1。
- Range_lookup (可选):一个逻辑值,用于确定您希望 VLOOKUP 查找近似匹配还是精确匹配。
- 近似匹配 - 将此参数设置为 TRUE、1 或留空。重要提示:要查找近似匹配,表格区域第一列的值必须按升序排序,以防 VLOOKUP 返回错误结果。
- 精确匹配 - 将此参数设置为 FALSE 或0。
示例1:VLOOKUP 中的精确匹配与近似匹配
如果您在使用 VLOOKUP 时对精确匹配和近似匹配感到困惑,本节可以帮助您消除这种困惑。
VLOOKUP 中的精确匹配
在此示例中,我将根据区域 E6:E8 中列出的分数查找相应的名称,因此我在单元格 F6 中输入以下公式,并将自动填充柄拖动到 F8。在此公式中,最后一个参数被指定为 FALSE以执行精确匹配查找。
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
然而,由于分数98 不存在于数据区域的第一列中,VLOOKUP 返回 #N/A 错误结果。
VLOOKUP 中的近似匹配
仍然使用上述示例,如果您将最后一个参数更改为 TRUE,VLOOKUP 将执行近似匹配查找。如果未找到匹配项,它将查找小于查找值的下一个最大值并返回相应的结果。
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
由于分数98 不存在,VLOOKUP 查找小于98 的下一个最大值,即95,并返回分数95 的名称作为最接近的结果。
- 在此近似匹配情况下,表格区域第一列的值必须按升序排序。否则,VLOOKUP可能无法返回正确的值。
- 在 VLOOKUP 函数中,我锁定了表格区域 ($B$6:$C$12),以便快速引用针对多个查找值的一致数据集。
示例2:使用 VLOOKUP进行多条件查找
本节演示如何在 Excel 中使用 VLOOKUP进行多条件查找。如下面的截图所示,如果您尝试根据提供的名称(在单元格 H5 中)和部门(在单元格 H6 中)查找工资,请按照以下步骤完成。
步骤1:添加辅助列以连接查找列中的值
在这种情况下,我们需要创建一个辅助列来连接名称列和部门列中的值。
- 在数据区域的左侧添加一个辅助列,并为此列添加标题。请参见截图:
- 在此辅助列中,选择标题下的第一个单元格,在 编辑栏(F)中输入以下公式,并按 Enter.
=C6&" "&D6
备注:在此公式中,我们使用 & 符号连接两列中的文本以生成一段文本。- C6 是要连接的 名称 列的第一个名称,D6 是要连接的 部门 列的第一个部门。
- 这两个单元格的值之间用空格连接。
- 选择此结果单元格,然后拖动 自动填充柄 向下应用此公式到同一列的其他单元格。
步骤2:应用 VLOOKUP 函数并给出条件
选择一个您想输出结果的单元格(此处我选择 I7),在编辑栏(F)中输入以下公式,然后按 Enter。
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
结果
- 辅助列必须用作数据区域的第一列。
- 现在工资列是数据区域的第五列,因此我们在公式中使用数字 5作为列索引。
- 我们需要以与辅助列相同的方式连接 I5 和 I6 (I5 & " " & I6),并将连接后的值用作公式中的 查找值 参数。
- 您也可以直接在查找值参数中放入两个条件,并用空格分隔它们(如果条件是文本,请不要忘记用双引号括起来)。
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- 更好的替代方案 - 秒内多条件查找Kutools for Excel 的多条件查找功能可以帮助您轻松在几秒钟内进行多条件查找。立即获取30 天全功能免费试用!
常见 VLOOKUP 错误及解决方案
本节列出了您在使用 VLOOKUP 时可能遇到的常见错误,并提供了解决这些错误的方法。
常见 VLOOKUP 错误概述: | ||||
原因1:查找值不在第一列 | ||||
原因2:查找值未找到 | ||||
------ | 原因3:查找值小于最小值 | |||
原因4:数字格式为文本 | ||||
原因5:表格区域不固定 | ||||
------ | 原因1:查找值超过255 个字符 | |||
原因2:列索引小于1 | ||||
------ | 原因1:列索引大于列数 | |||
------ | 原因1:查找列未按升序排序 | |||
原因2:插入或删除了列 | ||||
返回 #N/A 错误
VLOOKUP 最常见的错误是 #N/A 错误,这意味着 Excel 找不到您要查找的值。以下是 VLOOKUP可能返回 #N/A 错误的一些原因。
原因1:查找值不在表格区域的第一列
Excel VLOOKUP 的一个限制是它只允许从左到右查找。因此,查找值必须在表格区域的第一列中。
如下面的截图所示,我想根据给定的职称返回一个名称。这里查找值(销售经理)在表格区域的第二列,而返回值在查找列的左侧,因此 VLOOKUP 返回 #N/A 错误。
解决方案
您可以应用以下任何解决方案来修复此错误。
- 重新排列列您可以重新排列列,将查找列放在表格区域的第一列。
- 一起使用 INDEX 和 MATCH 函数在这里,我们将 INDEX 和 MATCH 函数一起使用,作为 VLOOKUP 的替代方案来解决此问题。
=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- 使用 XLOOKUP 函数(适用于 Excel365、Excel2021及更高版本)
=XLOOKUP(F6,C6:C12,B6:B12)
原因2:查找值在查找列中未找到(精确匹配)
VLOOKUP 返回 #N/A 错误的最常见原因之一是您要查找的值未找到。
如下面的示例所示,我们将根据 E6 中给定的分数98 查找名称。然而,此分数不存在于数据区域的第一列中,因此 VLOOKUP 返回 #N/A 错误结果。
解决方案
要修复此错误,您可以尝试以下解决方案之一。
- 如果您希望 VLOOKUP 搜索小于查找值的下一个最大值,请将最后一个参数 FALSE(精确匹配)更改为 TRUE(近似匹配)。有关更多信息,请参见示例1:使用 VLOOKUP进行精确匹配与近似匹配。
- 为了避免更改最后一个参数并在查找值未找到时获得提醒,您可以将 VLOOKUP 函数包含在 IFERROR 函数中:
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
原因3:查找值小于查找列中的最小值(近似匹配)
如下面的截图所示,您正在执行近似匹配查找。您要查找的值(此例中的 ID号码1001)小于查找列中的最小值1002,因此 VLOOKUP 返回 #N/A 错误。
解决方案
这里有两个解决方案供您选择。
- 确保查找值大于或等于查找列中的最小值。
- 如果您希望 Excel 提醒您查找值未找到,只需将 VLOOKUP 函数嵌套在 IFERROR 函数中,如下所示:
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
原因4:数字格式为文本
如您在下面的截图中所见,此示例中的 #N/A 错误结果是由于查找单元格(G6)与原始表格的查找列(B6:B12)之间的数据类型不匹配造成的。这里 G6 中的值是一个数字,而 B6:B12 区域中的值是格式为文本的数字。
解决方案
要解决此问题,您需要将查找值转换回数字。这里有两种方法供您选择。
- 应用“转换为数值”功能单击您要将文本转换为数字的单元格,选择此按钮
在单元格旁边,然后选择 转换为数值.
- 应用一个方便的工具批量转换文本和数字之间Kutools for Excel 的“文本与数值之间的转换”功能可以帮助您轻松地将一系列单元格从文本转换为数字,反之亦然。立即获取30 天全功能免费试用!
原因5:拖动 VLOOKUP公式到其他单元格时表格区域不固定
如下面的截图所示,E6 和 E7 中有两个查找值。在 F6 中获得第一个结果后,将 VLOOKUP公式从单元格 F6 拖动到 F7,返回了 #N/A 错误结果。这是因为单元格引用(B6:C12)默认是相对的,并在您向下移动行时进行了调整。表格区域已下移至 B7:C13,不再包含查找分数73。
解决方案
您需要锁定表格区域,通过在单元格引用的行和列前添加 $ 符号来保持其固定。要了解有关 Excel 中绝对引用的更多信息,请查看本教程:Excel绝对引用(如何制作和使用)。
返回 #VALUE 错误
以下条件可能导致 VLOOKUP 返回 #VALUE 错误结果。
原因1:查找值超过255 个字符
如下面的截图所示,单元格 H4 中的查找值超过255 个字符,因此 VLOOKUP 返回 #VALUE 错误结果。
解决方案
要解决此限制,您可以应用其他可以处理更长字符串的查找函数。尝试以下公式之一。
- INDEX 和 MATCH:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- XLOOKUP 函数 (适用于 Excel365、Excel2021及更高版本):
=XLOOKUP(H4,B5:B11,E5:E11)
原因2:列索引参数小于1
列索引指定表格区域中包含您要返回的值的列号。此参数必须是对应于表格区域中有效列的正数。
如果您输入的列索引小于1(即零或负数),VLOOKUP 将无法在表格区域中定位列。
解决方案
要解决此问题,请确保 VLOOKUP公式中的列索引参数是对应于表格区域中有效列的正数。
返回 #REF 错误
本节列出了 VLOOKUP 返回 #REF 错误的一个原因,并提供了解决此问题的方法。
原因:列索引参数大于列数
如您在下面的截图中所见,表格区域只有4 列。然而,您在 VLOOKUP公式中指定的列索引是5,大于表格区域中的列数。因此,VLOOKUP 将无法定位列,并最终返回 #REF 错误。
解决方案
- 指定正确的列号 确保 VLOOKUP公式中的列索引参数是对应于表格区域中有效列的数字。
- 根据指定的列标题自动获取列号 如果表格包含许多列,您可能难以确定正确的列索引号。在这里,您可以将 MATCH 函数嵌套在 VLOOKUP 函数中,以根据某个列标题找到列的位置。
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
备注:在上述公式中,MATCH("Email",B5:E5,0) 函数用于获取数据区域 B6:E12 中 "Email" 列的列号。这里的结果是4,用作 VLOOKUP 函数中的列索引。
返回不正确的值
如果您发现 VLOOKUP 未返回正确的结果,可能是由于以下原因造成的
原因1:查找列未按升序排序
如果您将最后一个参数设置为 TRUE(或留空)以进行近似匹配,并且查找列未按升序排序,则结果值可能不正确。
解决方案
按升序排序查找列可以帮助您解决此问题。要执行此操作,请按照以下步骤操作:
- 选择查找列中的数据单元格,转到数据选项卡,在排序和筛选组中单击 从小到大排序。
- 在排序警告对话框中,选择展开选择选项,然后单击确定。
原因2:插入或删除了列
如下面的截图所示,我最初想要返回的值在表格区域的第四列,因此我将列索引号指定为4。由于插入了新列,结果列成为表格区域的第五列,导致 VLOOKUP 从错误的列返回结果。
解决方案
这里有两个解决方案供您选择。
- 您可以手动更改列索引号以匹配返回列的位置。此处的公式应更改为:
=VLOOKUP(H6,B6:F12,5,FALSE)
- 如果您始终希望从某个特定列返回结果,例如此示例中的 Email 列。以下公式可以帮助根据给定的列标题自动匹配列索引,无论是否从表格区域中插入或删除列。
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
其他函数备注
- VLOOKUP仅从左到右查找值。查找值在最左列中,结果值应在查找列右侧的任意列中。
- 如果您将最后一个参数留空,VLOOKUP 默认使用近似匹配。
- VLOOKUP 执行不区分大小写的查找。
- 对于多个匹配项,VLOOKUP仅返回在表格区域中按行顺序找到的第一个匹配项。
相关文章
20+ VLOOKUP 示例适用于 Excel 初学者和高级用户 本教程通过几十个基本和高级示例逐步演示如何在 Excel 中使用 VLOOKUP 函数。
从右到左查找 如果您想在其他列中查找特定值并返回左侧的相对值,本教程中的方法可以帮助您完成此任务。
从下到上查找 本教程提供了两种方法,帮助您从下到上查找匹配值。
执行区分大小写的查找 如果您想在 Excel 中执行区分大小写的 VLOOKUP,本教程中的方法可以帮助您。
VLOOKUP 保持源格式 本教程提供了一种方法,帮助您在 Excel 中执行 VLOOKUP 时保留结果单元格的所有格式。
最佳 Office 办公效率工具
🤖 | Kutools AI 助手:基于智能执行,彻底革新数据分析 |生成代码|创建自定义公式|分析数据并生成图表|调用 Kutools Functions… |
热门功能:查找、选中项的背景色或标记重复项|删除空行|合并列或单元格且不丢失数据|四舍五入(无公式)... | |
高级 LOOKUP:多条件查找 (VLookup)|多值查找 (VLookup)|多表查找 (VLookup Across Multiple Sheets)|模糊查找 (Fuzzy Lookup)... | |
高级下拉列表:快速创建下拉列表|依赖型下拉列表|多选下拉列表... | |
列管理器:添加指定数量的列 |移动列 |切换隐藏列的可见状态| 比较区域及列... | |
特色功能:网格聚焦|设计视图|增强编辑栏|工作簿 & 工作表管理器|资源库(自动文本)|日期提取|合并数据|加密/解密单元格|按列表发送电子邮件|超级筛选|特殊筛选(筛选粗体/倾斜/删除线等)... | |
热门15 大工具集:12 款文本工具(添加文本、删除特定字符等)|50+ 种图表 类型(甘特图等)|40+ 实用公式(基于生日计算年龄等)|19 款插入工具(插入二维码、按路径插入图片等)|12 种转换工具(小写金额转大写、汇率转换等)|7 款合并与分割工具(高级合并行、分割单元格等)|...更多精彩等你发现 |
用 Kutools for Excel 加速你的 Excel 技能,体验前所未有的高效办公。 Kutools for Excel 提供300 多项高级功能,助您提升效率,节省大量时间。点击此处,获取你最需要的功能...
Office Tab 为 Office 带来标签式界面,让你的工作更加轻松
- 在 Word、Excel、PowerPoint 启用标签式编辑和阅读
- 在同一窗口的新标签中打开和创建多个文档,无需新建窗口。
- 办公效率提升50%,每天帮你减少上百次鼠标点击!