跳到主要内容

Excel VLOOKUP函数

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 根据表数组中行的顺序仅返回它在表数组中找到的第一个匹配项。

最佳办公生产力工具

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

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

kte选项卡201905


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

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!