跳到主要内容

Excel VLOOKUP函数

添加一名作者 最后修改时间:2023-06-01

我们推荐使用 Excel VLOOKUP 函数 是一个功能强大的工具,可帮助您通过垂直匹配表格或范围的第一列来查找指定值,然后从同一行的另一列返回相应的值。 尽管 VLOOKUP 非常有用,但对于初学者来说有时很难掌握。 本教程旨在通过提供帮助您掌握 VLOOKUP 论据的逐步解释, 有用的例子常见错误的解决方法 你在使用 VLOOKUP 函数时可能会遇到。


相关视频


论证的逐步解释

如上图所示,VLOOKUP 函数用于根据给定的 ID 号查找电子邮件。 我现在将通过逐步分解每个参数来详细说明如何在此示例中使用 VLOOKUP。

第一步:启动VLOOKUP函数

选择一个单元格(在本例中为 H6)输出结果,然后通过在其中键入以下内容来启动 VLOOKUP 函数 配方栏.

=VLOOKUP(
第 2 步:指定查找值

首先,在 VLOOKUP 函数中指定查找值(这就是您要查找的值)。 在这里,我引用了包含特定 ID 号 6 的单元格 G1005。

=VLOOKUP(G6

备注: 查找值必须在数据范围的第一列中。
第三步:指定表数组

接下来,指定包含您要查找的值和要返回的值的单元格区域。 在本例中,我选择范围 B6:E12。 公式现在显示如下:

=VLOOKUP(G6,B6:E12

备注:如果你想复制VLOOKUP函数在同一列中查找多个值并得到不同的结果,你需要通过添加美元符号来使用绝对引用,就像这样:
=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

按回车键得到结果

通过对上述示例中的每个参数一一解释,VLOOKUP 函数的语法和参数现在更容易理解。


语法和参数

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

  • 查找值 (必需):您要查找的值(实际值或单元格引用)。 请记住,此值必须位于 table_array 的第一列中。
  • 表格数组 (必填):单元格范围包含查找值的列和返回值的列。
  • 列索引 (必需):整数表示包含返回值的列号。 它从 table_array 最左边的列的数字 1 开始。
  • 范围查找 (可选):一个逻辑值,用于确定您希望 VLOOKUP 查找近似匹配还是精确匹配。
    • 近似匹配 - 将此参数设置为 TRUE, 1 或离开它 空白.
      重要:要找到近似匹配,table_array 的第一列中的值必须按升序排序,以防 VLOOKUP 返回错误结果。
    • 完全符合 - 将此参数设置为 FALSE or 0.

例子

本节通过一些示例来帮助您更全面地了解VLOOKUP函数。

示例 1:VLOOKUP 中的精确匹配与近似匹配

如果您在使用 VLOOKUP 时对完全匹配和近似匹配感到困惑,本节可以帮助您消除这种困惑。

VLOOKUP 中的精确匹配

在此示例中,我将根据 E6:E8 范围内列出的分数查找相应的名称,因此我在单元格 F6 中输入以下公式并将自动填充句柄向下拖动到 F8。 在此公式中,最后一个参数指定为 FALSE 执行精确匹配查找。

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

但是,由于数据范围的第一列中不存在分数 98,因此 VLOOKUP 返回 #N/A 错误结果。

备注:这里我在 VLOOKUP 函数中锁定了表数组($B$6:$C$12),以便快速引用一个 一贯 针对多个查找值的数据集。
VLOOKUP 中的近似匹配

仍然使用上面的示例,如果将最后一个参数更改为 TRUE, VLOOKUP 将执行近似匹配查找。 如果没有找到匹配,它会找到下一个小于查找值的最大值并返回相应的结果。

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

由于分数 98 不存在,VLOOKUP 查找下一个小于 98 的最大值,即 95,并返回分数 95 的名称作为最接近的结果。

:
  • 在这种近似匹配的情况下,table_array 第一列中的值必须按升序排序。 否则,VLOOKUP 可能不会返回正确的值。
  • 在这里,我在 VLOOKUP 函数中锁定了表数组 ($B$6:$C$12),以便针对多个查找值快速引用一组一致的数据。

示例 2:使用具有多个条件的 VLOOKUP

本节演示如何在 Excel 中使用具有多个条件的 VLOOKUP。 如下面的屏幕截图所示,如果您尝试根据提供的名称(在单元格 H5 中)和部门(在单元格 H6 中)查找薪水,请按照以下步骤完成。

第 1 步:添加辅助列以连接查找列中的值

在这种情况下,我们需要创建一个辅助列来连接来自 名称 列和 问题类型 列。

  1. 在数据范围的左侧添加一个辅助列,并为此列提供一个标题。 看截图:
  2. 在此辅助列中,选择标题下的第一个单元格,在 公式栏,然后按 输入.
    =C6&" "&D6
    :在此公式中,我们使用与号 (&) 将两列中的文本连接起来以生成单段文本。
    • C6 是的名字 名称 专栏加入, D6 是第一部门 问题类型 列加入。
    • 这两个单元格的值连接在一起,中间有一个空格。
  3. 选择此结果单元格,然后将 自动填充句柄 向下将此公式应用于同一列中的其他单元格。
第 2 步:应用具有给定条件的 VLOOKUP 函数

选择一个要输出结果的单元格(这里我选择I7),在其中输入如下公式 公式栏,然后按 输入.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
结果

:
  • 辅助列必须用作数据范围的第一列。
  • 现在工资列是数据范围的第五列,所以我们使用数字 5 作为公式中的列索引。
  • 我们需要加入标准 I5I6 (I5& " "&I6) 与辅助列相同的方式,并使用连接值作为 Lookup_Array中 公式中的参数。
  • 您也可以将这两个条件直接放在 lookup_value 参数中,并用空格分隔它们(如果条件是文本,请不要忘记用双引号将它们括起来)。
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • 更好的选择 - 在几秒钟内使用多个条件进行查找
    我们推荐使用 多条件查找 的特点 Kutools for Excel 可以帮助您轻松地在几秒钟内使用多个条件进行查找。 立即获得 30 天的全功能免费试用!

VLOOKUP常见错误及解决方法

本节列出了您在使用 VLOOKUP 时可能遇到的常见错误,并提供了修复这些错误的解决方案。

  常见 VLOOKUP 错误概述:
          
         原因一:查找值不在第一列  
     原因二:未找到查找值  
  ------  原因三:查找值小于最小值  
     原因 4:数字被格式化为文本  
       原因 5:Table_array 不是常量  
         
  ------  原因 1:查找值超过 255 个字符  
   原因二:col_index小于2  
         
  ------  原因一:col_index大于列数  
   
         
  ------  原因一:查找列未按升序排序  
   原因2:插入或删除列  
         

#N/A 返回错误

VLOOKUP 最常见的错误是#N/A 错误,这意味着 Excel 无法找到您要查找的值。 以下是 VLOOKUP 可能返回 #N/A 错误的一些原因。

原因一:查找值不在table_array的第一列

Excel VLOOKUP 的限制之一是它只允许您从左向右查看。 因此,查找值必须在 table_array 的第一列中。

如下图所示,我想根据给定的职位返回一个名字。 这里的查找值(销售经理) 在 table_array 的第二列中,返回值在查找列的左侧,因此 VLOOKUP 返回 #N/A 错误。

解决方案

您可以应用以下任何解决方案来修复此错误。

  • 重新排列列
    您可以重新排列列以将查找列放在 table_array 的第一列中。
  • 一起使用 INDEX 和 MATCH 函数
    这里我们使用 INDEX 和 MATCH 函数一起作为 VLOOKUP 的替代来解决这个问题。
    =INDEX(B6:B12,MATCH(F6,C6:C12,0))
  • 使用XLOOKUP函数(适用于Excel 365、Excel 2021及更高版本)
    =XLOOKUP(F6,C6:C12,B6:B12)

原因二:在查找列中没有找到查找值(精确匹配)

VLOOKUP 返回#N/A 错误的最常见原因之一是找不到您要查找的值。

如下例所示,我们将根据 E98 中给定的 6 分来查找名称。 但是,该分数在数据范围的第一列中不存在,因此 VLOOKUP 返回 #N/A 错误结果。

解决方案

要修复此错误,您可以尝试以下解决方案之一。

  • 如果要 VLOOKUP 搜索小于查找值的下一个最大值,请更改最后一个参数 FALSE (完全匹配)到 TRUE (近似匹配)。 欲了解更多信息,请参阅 示例 1:使用 VLOOKUP 的精确匹配与近似匹配.
  • 为避免更改最后一个参数并在未找到查找值时获得提醒,您可以将 VLOOKUP 函数包含在 IFERROR 函数中:
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

原因三:查找值小于查找列中的最小值(近似匹配)

如下面的屏幕截图所示,您正在执行近似匹配查找。 您要查找的值(在本例中为 ID 号 1001)小于查找列中的最小值 1002,因此,VLOOKUP 返回 #N/A 错误。

解决方案

这里有两种解决方案供您使用。

  • 确保查找值大于或等于查找列中的最小值。
  • 如果想让Excel提示找不到查找值,只需要在IFERROR函数中嵌套VLOOKUP函数,如下:
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

原因 4:数字被格式化为文本

正如您在下面的屏幕截图中看到的,此示例中的 #N/A 错误结果是由于原始表的查找单元格 (G6) 和查找列 (B6:B12) 之间的数据类型不匹配造成的。 这里 G6 中的值是一个数字,B6:B12 范围内的值是格式化为文本的数字。

Tips::如果将数字转换为文本,单元格左上角会显示一个绿色小三角形。

解决方案

要解决这个问题,您需要将查找值转换回数字。 这里有两种方法供您使用。

  • 应用转换为数字功能
    单击要将文本转换为数字的单元格,选择此按钮  在单元格旁边,然后选择 转换成数字.
  • 应用一个方便的工具在文本和数字之间批量转换
    我们推荐使用 在文字和数字之间转换 的特点 Kutools for Excel 帮助您轻松地将一系列单元格从文本转换为数字,反之亦然。 立即获得 30 天的全功能免费试用!

原因五:拖拽VLOOKUP公式到其他单元格时table_array不是常量

如下截图所示,E6和E7中有两个查找值。 F6得到第一个结果后,将VLOOKUP公式从F6单元格拖到F7,返回#N/A错误结果。 这是因为单元格引用 (B6:C12) 默认情况下是相对的,并且会在您向下移动行时进行调整。 表数组已向下移动到 B7:C13,其中不再包含查找分数 73。

解决方案

您需要通过添加一个来锁定表数组以使其保持不变 $ 在单元格引用中的行和列之前签名。 要了解有关 Excel 中绝对引用的更多信息,请查看本教程: Excel绝对参考(制作及使用方法).

#VALUE 返回错误

以下情况可能会导致 VLOOKUP 返回#VALUE 错误结果。

原因一:查找值超过1个字符

如下图所示,单元格H4中的查找值超过255个字符,因此VLOOKUP返回#VALUE错误结果。

解决方案

要解决此限制,您可以应用可以处理更长字符串的不同查找函数。 尝试以下公式之一。

  • 索引和匹配:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
  • XLOOKUP 函数 (适用于 Excel 365、Excel 2021 及更高版本):
    =XLOOKUP(H4,B5:B11,E5:E11)

原因 2:col_index 参数小于 1

列索引指定表数组中包含要返回的值的列号。 此参数必须是与表数组中的有效列相对应的正数。

如果您输入的列索引小于 1(即零或负数),VLOOKUP 将无法在表数组中找到该列。

解决方案

要解决此问题,请确保 VLOOKUP 公式中的列索引参数是与表数组中的有效列相对应的正数。

#REF 返回错误

本节列出了 VLOOKUP 返回 #REF 错误的一个原因并提供了解决此问题的方法。

原因:col_index 参数大于列数

正如您在下面的屏幕截图中所见,表格数组只有 4 列。 但是,您在 VLOOKUP 公式中指定的列索引为 5,这大于表数组中的列数。 因此,VLOOKUP 将无法找到列并最终返回 #REF 错误。

解决方案

  • 指定正确的列号
    确保 VLOOKUP 公式中的列索引参数是与表数组中的有效列相对应的数字。
  • 根据指定的列标题自动获取列号
    如果表包含很多列,您可能无法确定正确的列索引号。 在这里,您可以将 MATCH 函数嵌套在 VLOOKUP 函数中,以根据特定的列标题查找列的位置。
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    备注:在上面的公式中, MATCH("邮箱",B5:E5, 0) 函数用于获取“电子邮件"日期范围 B6:E12 中的列。此处结果为 4,用作 VLOOKUP 函数中的 col_index。

返回的值不正确

如果发现VLOOKUP返回的结果不正确,可能是以下原因造成的

原因一:查找列未按升序排序

如果您将最后一个参数设置为 TRUE (或 留空) 对于近似匹配,并且查找列未按升序排序,则结果值可能不正确。

解决方案

按升序对查找列进行排序可以帮助您解决这个问题。 为此,请按照以下步骤操作:

  1. 选择查找列中的数据单元格,转到 时间 标签,点击 最小到最大排序 ,在 排序和过滤 组。
  2. 排序警告 对话框中,选择 扩大选择 选项,然后单击 OK.

原因2:插入或删除列

如下图所示,我原本要返回的值在表数组的第四列,所以我指定col_index为4,因为插入了新的一列,结果列变成了表的第五列数组,导致 VLOOKUP 从错误的列返回结果。

解决方案

这里有两种解决方案供您使用。

  • 您可以手动更改列索引号以匹配返回列的位置。 这里的公式应该改为:
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • 如果你总是想从一个 certian 列返回结果,比如本例中的 Email 列。 下面的公式可以帮助根据给定的列标题自动匹配列索引,而不管列是从表数组中插入还是删除。
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

其他功能说明

  • VLOOKUP 只从左到右查找值。
    查找值位于最左侧的列中,结果值应位于查找列右侧的任意列中。
  • 如果将最后一个参数留空,VLOOKUP 默认使用近似匹配。
  • VLOOKUP 执行不区分大小写的查找。
  • 对于多个匹配项,VLOOKUP 根据表数组中行的顺序仅返回它在表数组中找到的第一个匹配项。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations