如何在 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 列。
- 点击结果单元格。 例如 D2,位于第一个订单旁边。
- 开始输入公式。 输入 =VLOOKUP(。
- 添加查找值。 点击 B2(产品代码),然后输入逗号。
- 添加表格范围。 选择 F2:H5,按 F4 键使其变为 $F$2:$H$5,然后输入逗号。
- 添加列号。 输入 3 代表价格列,然后输入逗号。
- 选择匹配类型。 输入 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 并返回第三列。
最简单的构建方法是通过点击:
- 开始输入 =VLOOKUP( 并点击查找单元格。
- 输入一个逗号,然后点击另一个工作表的标签。
- 选择该工作表上的表格范围,并按 F4 键锁定它。
- 完成列号和 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。
第 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 查找值。