超级促销周Claude Skills——20% 折扣
Tips

如何在 Excel 中使用 VLOOKUP:完整指南

Powerdrill Bloom·
如何在 Excel 中使用 VLOOKUP:完整指南

要在 Excel 中执行 VLOOKUP,请在单元格中输入 =VLOOKUP( 并添加四个部分。它们分别是:要查找的值、要搜索的表格、要返回的列号,以及表示精确匹配的 FALSE。例如,=VLOOKUP(B2,$F$2:$H$5,3,FALSE) 会查找 B2 中的值,并返回表格的第三列。

本指南涵盖了 VLOOKUP 的工作原理、如何逐步构建公式,以及如何从另一个工作表中查找值。它还介绍了精确匹配与近似匹配、常见错误,以及何时应改用 XLOOKUP。步骤参考自 Microsoft 支持页面,阅读日期为 2026 年 11 月 13 日。

VLOOKUP 的作用

VLOOKUP 在表格的第一列中查找一个值,并返回同一行中的另一个值。“V”代表垂直(vertical),因为它是在一列中向下搜索的。

Microsoft 的支持页面给出了两个典型用途。你可以“通过零件编号查找汽车零件的价格”。你也可以“根据员工 ID 查找员工姓名”。

常见任务包括:

  • 价格。 通过产品代码将产品价格提取到订单列表中。
  • 姓名。 通过员工 ID 将员工姓名导入报告中。
  • 类别。 为销售导出的每一行添加区域或部门。
  • 比率。 从查找表中查找税率或佣金率。
  • 匹配列表。 检查一个列表中的哪些项目出现在另一个列表中。

VLOOKUP 有一条决定一切的规则。Microsoft 称之为“VLOOKUP 的秘诀”。你必须整理数据,使你要查找的值“位于你想要查找的返回值(金额)的左侧”。

VLOOKUP 公式的工作原理

语法包含四个部分。Microsoft 将其列为:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

部分 含义 示例
lookup_value 你想要查找的值 B2,即产品代码
table_array 要搜索的范围,查找值位于其第一列 $F$2:$H$5
col_index_num 该范围内要返回的列,从 1 开始计数 3,代表价格列
range_lookup FALSE 表示精确匹配,TRUE 表示近似匹配 FALSE

Microsoft 通过一个例子解释了列计数。以 B2:D11 作为范围,“你应该将 B 计为第一列,C 计为第二列,依此类推”。

最后一部分是可选的,但不填会有风险。Microsoft 表示:“如果你不指定任何内容,默认值将始终为 TRUE 或近似匹配。”对于大多数查找(如 ID 和代码),你需要的是 FALSE。

先设置好你的数据

大多数 VLOOKUP 问题源于数据,而非公式。进行几项检查可以节省以后的时间。

  • 将查找列放在首位。 你搜索的值必须位于表格范围的最左侧列。
  • 每行保持一个值。 每个代码或 ID 在查找表中应只出现一次。
  • 将数字存储为数字。 Microsoft 警告说,如果数字被存储为文本,“VLOOKUP 可能会返回不正确或意外的值”。
  • 删除多余空格。 前导或尾随空格会导致匹配失败。TRIM 函数可以删除它们。
  • 使用标题。 清晰的标题使计算列号变得更容易。

如果你的查找列位于所需值的右侧,VLOOKUP 将无法触及它。你可以移动该列,或者使用稍后介绍的 XLOOKUP。

还有两个习惯能让查找更易于维护。首先,将查找表格式化为 Excel 表格,或给它起个名字。Microsoft 指出,对于 table_array,“你可以使用命名范围或表格”。像 Products 这样的命名范围比 $F$2:$H$5 更易读。其次,将查找表放在独立的工作表中。这样在编辑主数据时,就不会有人意外排序或删除行。

如何逐步执行 VLOOKUP

以下是基本精确匹配查找的完整过程。在此示例中,订单列表位于 A 到 C 列,产品表位于 F 到 H 列。

  1. 点击结果单元格。 例如 D2,位于第一个订单旁边。
  2. 开始输入公式。 输入 =VLOOKUP(。
  3. 添加查找值。 点击 B2(产品代码),然后输入逗号。
  4. 添加表格范围。 选择 F2:H5,按 F4 键使其变为 $F$2:$H$5,然后输入逗号。
  5. 添加列号。 输入 3 代表价格列,然后输入逗号。
  6. 选择匹配类型。 输入 FALSE,闭合括号,然后按 Enter 键。

完成后的公式为 =VLOOKUP(B2,$F$2:$H$5,3,FALSE)。

然后将公式向下填充到整列。双击 D2 角落的填充柄,Excel 就会将其复制到最后一行。

第 4 步对于填充至关重要。Microsoft 的最佳实践建议使用绝对引用,这样你就可以“向下填充公式,使其始终查看完全相同的查找范围”。如果没有美元符号,范围会每次向下移动一行,导致后面的查找失败。

在两个工作表之间进行 VLOOKUP

查找表通常位于另一个工作表中。VLOOKUP 通过在范围中包含工作表名称来处理这种情况。

Microsoft 的页面包含这个示例:

=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)

这里,'Client Details' 是工作表名称,因为它包含空格,所以放在单引号中。A:F 是该工作表上的范围。该公式查找 A2 并返回第三列。

最简单的构建方法是通过点击:

  1. 开始输入 =VLOOKUP( 并点击查找单元格。
  2. 输入一个逗号,然后点击另一个工作表的标签。
  3. 选择该工作表上的表格范围,并按 F4 键锁定它。
  4. 完成列号和 FALSE,然后按 Enter 键。

Excel 会为你自动写入工作表名称。只要在构建公式时两个文件都处于打开状态,同样的方法也适用于另一个工作簿。

精确匹配 vs. 近似匹配

第四个参数会改变 VLOOKUP 的搜索方式。选错参数是导致错误答案的常见原因。

精确匹配 (FALSE 或 0)。 Microsoft 表示它“在第一列中搜索精确值”。如果该值不存在,结果为 #N/A。适用于 ID、代码、姓名以及任何必须完全匹配的内容。

近似匹配 (TRUE 或 1)。 Microsoft 表示它“假设表格中的第一列已按数字或字母顺序排序,然后搜索最接近的值”。适用于范围,如税级或成绩界限。

这是一个典型的近似匹配案例。佣金表列出了 0、10,000 和 50,000 的销售阈值,对应的比率为 2%、3% 和 5%。32,000 的销售额介于 10,000 和 50,000 之间,因此 VLOOKUP 返回 3%。

为了使其生效,阈值必须从小到大排序。Microsoft 警告说,如果第一列未排序,“返回值可能是你意想不到的内容”。

精确匹配还支持文本通配符。Microsoft 表示,在使用 FALSE 和文本查找值时,可以使用问号和星号。“问号匹配任意单个字符。星号匹配任意字符序列。”其示例 =VLOOKUP("Fontan?",B2:E7,2,FALSE) 可以找到以任何字母结尾的 Fontana。要搜索真实的问号或星号,请在前面输入波浪号 (~)。

修复常见的 VLOOKUP 错误

Microsoft 的页面列出了最常见的问题及其原因。

错误 出了什么问题 如何修复
#N/A 使用 FALSE 时,未找到精确值 检查拼写、空格和存储为文本的数字
#REF! 列号大于范围内的总列数 扩大范围或减小列号
#VALUE! 参数小于 1 检查列号是否为 1 或更大
#NAME? 公式中的文本通常缺少引号 将文本写在引号内,例如 "Fontana"
错误的值 在未排序的列上使用 TRUE 或空白匹配类型 使用 FALSE,或对第一列进行排序

对于预料之中的 #N/A 结果(例如尚未进入表格的新产品),可以将公式包装在 IFERROR 中:

=IFERROR(VLOOKUP(B2,$F$2:$H$5,3,FALSE),"Not found")

这会显示一个清晰的标签而不是错误。请谨慎使用。标签可能会掩盖真实的数据问题,例如拼写错误的代码。

Microsoft 还介绍了较新版本 Excel 中的 #SPILL! 错误。当查找值引用整列(如 A:A)时会发生这种情况。请改为引用单个单元格,如 A2。

VLOOKUP vs. XLOOKUP

Microsoft 现在建议使用一个更新的函数。其 VLOOKUP 页面称 XLOOKUP 为“VLOOKUP 的改进版本”。它补充说,XLOOKUP “可以在任何方向工作,并且默认返回精确匹配”。

XLOOKUP 的语法是:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

在 XLOOKUP 中,同样的价格查找为 =XLOOKUP(B2,$F$2:$F$5,$H$2:$H$5)。不需要计算列号。

功能 VLOOKUP XLOOKUP
方向 查找列必须在左侧 返回列可以在任何一侧
默认匹配 近似匹配 (TRUE) 精确匹配
列引用 你需要计算的一个数字 你选择的一个范围
未找到消息 需要 IFERROR 内置 if_not_found 参数
可用性 所有版本 Excel 2016 或 Excel 2019 中不可用

最后一行很重要。Microsoft 指出,“XLOOKUP 在 Excel 2016 和 Excel 2019 中不可用”。如果你与使用这些版本的人共享文件,VLOOKUP 仍然是更安全的选择。

对于旧版本,还有第三种选择。Microsoft 的查找指南指出,“VLOOKUP 函数只能从左到右查找值”。对于向左查找,它建议“改用 INDEX 和 MATCH 函数的组合”。MATCH 找到查找值的行号,而 INDEX 返回该行中任何列的值。公式较长,但它在每个版本的 Excel 中都有效。对于价格示例,它将是 =INDEX($H$2:$H$5,MATCH(B2,$F$2:$F$5,0))。

VLOOKUP 示例

这是一个小型办公用品订单。产品表位于 F1:H5,标题在第 1 行。

产品 ID 名称 价格
P-101 笔记本 4.50
P-102 钢笔 1.20
P-103 订书机 8.75
P-104 文件夹 2.30

订单列表位于 A1:C5。每个订单都有一个 ID、一个产品 ID 和一个数量。

订单 产品 ID 数量 价格 (VLOOKUP) 行总计
1001 P-103 2 8.75 17.50
1002 P-101 10 4.50 45.00
1003 P-104 5 2.30 11.50
1004 P-105 1 #N/A —

D2 中的公式为 =VLOOKUP(B2,$F$2:$H$5,3,FALSE),向下填充至 D5。E2 中的行总计为 =C2*D2。

订单 1004 返回 #N/A,因为 P-105 不在产品表中。这是有用的信息,而不是公式错误。需要有人添加该产品或更正代码。

三个有效行的总计为 17.50 + 45.00 + 11.50,即 74.00。一旦 P-105 被添加到表中,其价格就会自动出现。

使用 AI 在 Excel 中执行 VLOOKUP

当你匹配具有许多列的大型文件时,编写和检查查找公式会很耗时。AI 工具可以为你匹配表格并标记不匹配的行。

以下是使用 Powerdrill Bloom 的三个步骤。

第 1 步:上传你的 Excel 文件

上传两个文件,或者上传一个包含两个表格的工作簿。合并 CSV 文件页面指出,你可以“根据特定的列匹配、排序顺序或你定义的其他标准”来定制合并。

确保两个表格共享一个关键列,例如产品 ID 或员工 ID。

上传两个 Excel 文件以在 Powerdrill Bloom 中执行 VLOOKUP 风格的匹配

第 2 步:使用自然语言要求匹配

描述你需要的查找。例如:“根据产品 ID 将产品表中的价格添加到每个订单中,并列出任何没有匹配项的订单。”

对于匹配后的分析,Excel AI 助手会“自动识别 Excel 数据中的趋势、相关性和关键指标”。

第 3 步:检查结果并导出

抽查几行与源表格的对比情况,特别是任何未匹配的行。然后下载合并后的文件或索取摘要报告。未匹配 ID 的简短列表通常是最有用的输出,因为它显示了哪些记录需要在源头进行修复。

有关不使用公式合并文件的另一种方法,请参阅我们的指南:如何在不使用 VLOOKUP 的情况下连接两个 Excel 文件。

在导出合并文件之前检查匹配的行

要避免的常见错误

这些是 VLOOKUP 中最常出现的问题。

  • 遗漏匹配类型。 默认是近似匹配。对于 ID 和代码,请键入 FALSE。
  • 相对表格范围。 如果没有 $ 符号,范围会随着向下填充而移动。
  • 从工作表开始计算列。 应从范围的第一列开始计算,而不是从 A 列开始。
  • 查找列在右侧。 VLOOKUP 只能向右查找。移动该列或使用 XLOOKUP。
  • 隐藏的空格。 带有尾随空格的 "P-101 " 将无法匹配 "P-101"。
  • 数字存储为文本。 以文本形式输入的代码将无法匹配以数字形式存储的相同代码。
  • 重复键。 VLOOKUP 仅返回第一个匹配项,因此重复项会隐藏后面的行。
  • 隐藏所有错误。 IFERROR 很有用,但在共享文件之前请检查它隐藏了什么。

当你准备好在不使用公式的情况下匹配和分析大型文件时,请尝试使用订单导出和产品列表来体验 Powerdrill Bloom。

常见问题解答

如何在 Excel 中执行 VLOOKUP?

输入 =VLOOKUP(查找值, 表格范围, 列号, FALSE)。例如,=VLOOKUP(B2,$F$2:$H$5,3,FALSE) 在 F2:H5 的第一列中查找 B2 并返回第三列。

为什么我的 VLOOKUP 返回 #N/A?

在精确匹配的情况下,#N/A 表示未找到该值。请检查拼写错误、多余空格以及存储为文本的数字。

如何从另一个工作表执行 VLOOKUP?

在范围中包含工作表名称,例如 =VLOOKUP(A2,'Client Details'!A:F,3,FALSE)。在构建公式时点击另一个工作表会自动为你添加名称。

VLOOKUP 中的 FALSE 是什么意思?

FALSE 要求精确匹配。TRUE 或省略该参数则要求近似匹配,并且需要对第一列进行排序。

我应该使用 VLOOKUP 还是 XLOOKUP?

如果每个人都拥有 Excel for Microsoft 365、Excel 2021 或更高版本,请使用 XLOOKUP。它可以在任何方向工作,并且默认精确匹配。当文件要发送给使用 Excel 2016 或 Excel 2019 的人时,请使用 VLOOKUP。

来源: Microsoft 支持,VLOOKUP 函数 · Microsoft 支持,XLOOKUP 函数 · Microsoft 支持,使用 VLOOKUP、INDEX 或 MATCH 查找值。