Excel函数查找数据：VLOOKUP和XLOOKUP的用法区别

在 Excel 中，根据一个编号查出对应的姓名、价格或状态，是最常见的数据处理任务之一。VLOOKUP 和 XLOOKUP 都可以完成这类查找，但它们的默认匹配方式、查找方向、参数设计和版本兼容性并不相同。选错函数，轻则返回错误结果，重则公式表面正常、实际匹配到错误记录。

本文用一组简单数据讲清两种函数的基本写法、适用场景和关键边界，并说明如何处理查找不到、重复值、排序查找及公式安全问题。示例公式使用英文逗号作为参数分隔符；如果你的 Excel 区域设置使用分号，请将公式中的逗号改为分号。

一、先准备一份示例数据

假设工作表 A1:C5 中有以下产品表：第一列是产品编号，第二列是名称，第三列是单价。

假设 E2 中输入要查询的产品编号，例如 P1003。接下来，我们希望在 F2 中返回产品名称，在 G2 中返回单价。

二、VLOOKUP：按列查找的经典函数

1. 基本语法

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

各参数含义如下：

• lookup_value：要查找的值，例如 E2 中的产品编号。
• table_array：查找区域。VLOOKUP 会在这个区域的最左列中查找。
• col_index_num：要从查找区域的第几列返回结果，最左列计为第 1 列。
• range_lookup：匹配方式。FALSE 表示精确匹配；TRUE 表示近似匹配。此参数可以省略，但不建议省略。

查找名称时，A1:C5 区域的第 2 列是产品名称，因此公式为：

=VLOOKUP(E2, $A$2:$C$5, 2, FALSE)

查找单价时，返回区域中的第 3 列：

=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)

这里使用 FALSE 明确要求精确匹配。公式会在 A2:A5 中寻找与 E2 完全相符的编号，再从相同行的指定列返回值。如果 E2 是 P1003，名称公式返回“显示器”，单价公式返回 899。

2. VLOOKUP 最重要的限制：只能从最左列向右返回

VLOOKUP 只会在 table_array 的最左列查找，再从该区域右侧的列返回结果。比如想用产品名称反查编号，但名称在 B 列、编号在 A 列，下面的公式无法按预期工作：

=VLOOKUP("显示器", $A$2:$B$5, 1, FALSE)

原因是函数仍然会在区域 A2:B5 的最左列，也就是 A 列中寻找“显示器”，而不是自动转到 B 列查找。常见处理办法是调整数据列顺序、用 INDEX 与 MATCH 组合，或者在支持的 Excel 版本中改用 XLOOKUP。

3. VLOOKUP 的近似匹配及排序要求

当第四个参数为 TRUE 或省略时，VLOOKUP 使用近似匹配。此时查找列应按升序排列。函数会寻找小于或等于查找值的最大项目；如果查找值小于查找列中的最小值，通常会返回 #N/A。如果查找列没有按要求排序，结果可能不正确，而且未必会报错。

近似匹配适合按分数、金额等区间划档。例如，下面的分档表按起始分数升序排列：

如果分数放在 E2，分档表位于 A2:B5，可以使用：

=VLOOKUP(E2, $A$2:$B$5, 2, TRUE)

例如 E2 为 85，公式返回“良好”。这里第一列的 0、60、80、90 表示各档的起始值，而不是一组要逐项精确匹配的分数。对于产品编号、员工号、订单号等标识符，通常应该用精确匹配，不应依赖近似匹配。

需要特别注意：VLOOKUP 的第四个参数省略时，默认按近似匹配处理，并不等同于 FALSE。漏写这个参数是常见错误之一。为避免意外，按编号查找时应显式写出 FALSE。

4. 列序号的维护成本

col_index_num 是相对于查找区域的列序号，不是工作表的列号。例如区域从 B 列开始，区域中的第 2 列对应工作表 C 列。插入或调整区域中的列后，原有序号可能指向另一列，导致公式仍能计算、却返回了错误字段。

如果列序号大于所选区域的列数，VLOOKUP 会返回 #REF!。因此，修改表结构后应检查公式的查找区域和列序号，而不能只看公式是否出现错误提示。

三、XLOOKUP：更灵活的现代查找函数

1. 基本语法

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

参数含义如下：

• lookup_value：要查找的值。
• lookup_array：查找值所在的单行或单列。
• return_array：要返回结果所在的单行、单列或数组。
• if_not_found：找不到时返回的自定义内容；省略时通常返回 #N/A。
• match_mode：匹配模式。0 为精确匹配，-1 为精确匹配或下一个较小项，1 为精确匹配或下一个较大项，2 为通配符匹配。
• search_mode：搜索方向或搜索方式。1 为从首项到末项搜索，-1 为从末项到首项搜索，2 和 -2 分别表示按升序或降序进行二分搜索。

XLOOKUP 的精确匹配是默认行为，所以查找产品名称可以写成：

=XLOOKUP(E2, $A$2:$A$5, $B$2:$B$5)

查找单价则把返回区域改为 C2:C5：

=XLOOKUP(E2, $A$2:$A$5, $C$2:$C$5)

与 VLOOKUP 相比，XLOOKUP 把“在哪里找”和“从哪里返回”分成两个区域，不需要填写列序号。查找列可以在返回列的左侧，也可以在右侧。

2. 自定义“找不到”结果

默认情况下，找不到匹配项时，XLOOKUP 返回 #N/A。可以通过第四个参数设置提示内容：

=XLOOKUP(E2, $A$2:$A$5, $B$2:$B$5, "未找到该产品")

这适合给报表或普通使用者展示明确提示。不过，如果后续计算需要识别“未找到”这一状态，直接把错误值改成文字可能不合适：文字参与数值计算时可能造成新的问题。可以考虑保留错误，或者使用一个明确约定、不会与有效数据混淆的返回值。

VLOOKUP 没有内置的“找不到时返回内容”参数，通常用 IFNA 包裹：

=IFNA(VLOOKUP(E2, $A$2:$C$5, 2, FALSE), "未找到该产品")

IFNA 只处理 #N/A，而 IFERROR 会处理多种错误。若只想隐藏“没有匹配项”这一种情况，优先考虑 IFNA，以免把区域引用错误等其他问题一并掩盖。

3. 查找方向与重复值

XLOOKUP 默认从查找区域的首项向末项搜索，通常返回第一次匹配的结果。若一个编号重复出现，而你想取最后一次匹配，可以把 search_mode 设为 -1：

=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未找到", 0, -1)

这表示精确匹配，并从最后一项向前搜索。它适合查询“最近一次记录”等场景，但前提是数据排列顺序确实表达了时间或记录先后。它不会自行判断哪条记录在业务上更新，也不会检查重复数据是否合理。

VLOOKUP 对精确匹配通常返回查找列中第一条匹配记录，没有提供反向搜索参数。若必须取最后一条，通常需要换用其他函数或组合方案。

4. XLOOKUP 的近似匹配

XLOOKUP 可以明确指定精确匹配或邻近匹配。例如，按分数下限分档，可以使用“精确匹配或下一个较小项”：

=XLOOKUP(E2, $A$2:$A$5, $B$2:$B$5, "无对应等级", -1)

当分数为 85 时，如果 A2:A5 为按升序排列的 0、60、80、90，公式返回 80 对应的“良好”。使用 match_mode=-1 时，若没有完全相等的值，会寻找较小项；使用 match_mode=1 则会寻找较大项。边界处若不存在符合条件的邻近项，函数会按找不到的规则处理，可通过第四个参数提供提示。

不要把近似匹配和二分搜索混为一谈：前者由 match_mode 指定匹配规则，后者由 search_mode 指定搜索算法。普通精确查找不需要启用二分搜索。

5. 通配符查找

当 match_mode 为 2 时，XLOOKUP 支持通配符匹配。星号 * 可匹配零个或多个字符，问号 ? 可匹配单个字符，波浪号 ~ 用于转义通配符本身。例如，查找名称以“键”开头的第一条记录：

=XLOOKUP("键*", $B$2:$B$5, $A$2:$A$5, "未找到", 2)

如果要查找的文本本身包含星号或问号，而不是把它们当通配符使用，需要按通配符规则转义。若不需要模糊文本匹配，不要因为看到通配符示例就把 match_mode 设为 2；产品编号通常应做精确匹配。

6. 一次返回多列

在支持动态数组的 Excel 版本中，XLOOKUP 的返回区域可以包含多列。假设 A 列为编号，B、C 列分别是名称和单价，公式可以一次返回两个字段：

=XLOOKUP(E2, $A$2:$A$5, $B$2:$C$5, "未找到")

结果可能从输入公式的单元格向相邻单元格溢出。溢出区域如果已有内容，公式会出现 #SPILL!。在不支持动态数组的旧版 Excel 中，这种写法的表现可能不同；需要兼容旧版时，应逐项返回，或在实际目标版本中验证。

四、两种函数的主要区别

如果工作簿需要发给使用旧版 Excel 的同事，VLOOKUP 通常更容易兼容。若所有使用者的 Excel 都支持 XLOOKUP，且公式需要反向查找、明确设置未找到结果或返回多列，XLOOKUP 通常更直观。也可以根据组织使用的软件版本决定，而不是只按函数的新旧来选。

XLOOKUP 已在 Microsoft 365 及部分较新版本的 Excel 中提供，但并非所有早期版本都支持。旧版程序打开包含 XLOOKUP 的工作簿时，可能显示 #NAME? 等无法识别函数的错误。不同 Excel 产品、更新通道和兼容软件的支持情况可能不同，部署前应在目标环境中实际测试。

五、常见错误及排查方法

错误 1：查找结果看起来相同，实际数据类型不同

数字 100 和文本“100”看起来一样，但在某些查找情形下并不被视为相同值；编号前后的空格也会造成精确匹配失败。遇到 #N/A 时，先检查查找值和查找列是否存在前后空格、数字与文本混用、隐藏字符或格式不一致。单纯改变单元格显示格式，并不一定会把文本转换为数字。

可以用 TRIM 清理普通空格，但从网页或系统导出的数据还可能含有不间断空格等字符，需要先确认具体字符类型，再决定清理方法。不要直接批量转换标识符：像“00123”这样的编号，前导零可能是业务数据的一部分，转成数字后会丢失。

错误 2：忽略 VLOOKUP 的近似匹配默认值

按编号查找时，如果写成 =VLOOKUP(E2,$A$2:$C$5,2)，第四参数被省略，函数会使用近似匹配。结果可能不是目标编号对应的记录，尤其是查找列无序时。应明确写成 FALSE。

错误 3：把 VLOOKUP 的列序号当成工作表列号

公式的列序号从所选区域的第一列重新计数。例如，区域 D2:F100 中，D 列是第 1 列、E 列是第 2 列、F 列是第 3 列。把工作表上的列号直接填入参数，会返回错误列或导致 #REF!。

错误 4：XLOOKUP 的查找区域和返回区域尺寸不一致

常见的垂直查找中，查找区域与返回区域应当具有对应的行数；水平查找则应具有对应的列数。下面的范围行数不同，不是可靠的查找配置：

=XLOOKUP(E2, $A$2:$A$5, $B$2:$B$4)

应确保查找值与返回值按相同位置逐项对应，例如两边都覆盖第 2 至第 5 行。返回多列时，也要保证这些列与查找数组的记录方向一致。

错误 5：以为 XLOOKUP 会自动处理重复项

精确查找遇到重复值时，XLOOKUP 默认返回搜索方向上首先遇到的匹配项；默认搜索方向是从前到后。它不会自动汇总重复记录，也不会提示数据存在重复。若业务要求编号唯一，应先检查并治理重复数据，而不是把查找公式当作数据校验工具。

错误 6：二分搜索时数据没有按要求排序

XLOOKUP 的 search_mode=2 是按升序排列数据进行二分搜索，search_mode=-2 是按降序排列数据进行二分搜索。数据未按相应顺序排序时，结果可能不可靠。除非数据排序稳定、已验证且确有性能需要，否则使用默认的线性搜索通常更稳妥。普通工作表数据的查找不必为了“更快”而随意启用二分搜索。

错误 7：让“未找到”文字进入后续数值计算

把 #N/A 替换成“无数据”适合展示，却可能让后续的加法、平均值或数值分析变得复杂。设计公式时要区分展示层和计算层：如果后续还需计算，优先保留可识别的错误状态，或者使用符合业务约定的数值和单独的状态列。

六、查找方向之外的公式注意事项

将查找区域和返回区域写成绝对引用，例如 $A$2:$A$100，可以在复制公式时避免范围漂移。若数据行数会不断增加，可以使用 Excel 表格的结构化引用，或采用经过验证的动态范围方案。直接引用整列虽然方便，但工作簿复杂时可能增加计算负担；应根据数据量和实际性能测试选择范围。

对于水平查找，VLOOKUP 并不合适，因为它只在区域最左列查找。较旧工作簿可能用 HLOOKUP；支持 XLOOKUP 时，可以直接把一行作为查找区域、另一行作为返回区域。例如，标题在 A1:D1、对应数值在 A2:D2，按标题查询数值可写为：

=XLOOKUP(F1, $A$1:$D$1, $A$2:$D$2, "未找到")

查找函数只负责根据数据返回结果，不会验证表格的业务含义。比如某编号是否有效、两条记录冲突时应以哪条为准、金额是否已审批，都需要通过数据校验或业务规则另行处理。

七、公式示例汇总

以下公式均基于本文开头的示例区域，可直接按对应单元格布局使用：

VLOOKUP 精确查找产品名称：
=VLOOKUP(E2, $A$2:$C$5, 2, FALSE)

VLOOKUP 精确查找单价：
=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)

VLOOKUP 找不到时显示提示：
=IFNA(VLOOKUP(E2, $A$2:$C$5, 2, FALSE), "未找到该产品")

XLOOKUP 精确查找产品名称：
=XLOOKUP(E2, $A$2:$A$5, $B$2:$B$5)

XLOOKUP 精确查找单价，找不到时显示提示：
=XLOOKUP(E2, $A$2:$A$5, $C$2:$C$5, "未找到该产品")

XLOOKUP 从末项开始查找精确匹配：
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未找到", 0, -1)

八、如何选择

如果需要兼容较旧的 Excel，数据排列稳定，并且查找列在返回列左侧，VLOOKUP 仍然实用。使用时明确填写 FALSE 或 TRUE，并理解近似匹配的排序要求；修改表格列结构后，复核列序号。

如果 Excel 版本支持 XLOOKUP，且需要反向查找、从末项搜索、自定义未找到结果、通配符匹配或一次返回多列，优先考虑 XLOOKUP。它减少了列序号维护成本，但仍要求查找范围与返回范围正确对应；使用二分搜索时还必须保证排序符合要求。

无论选哪一种函数，都要确认数据类型和重复记录符合预期，并用边界样例测试：存在的值、缺失的值、重复值，以及区间的最小值和最大值。公式能计算出结果，不代表这个结果一定符合业务规则。

九、安全边界：查找公式不是权限控制

VLOOKUP 和 XLOOKUP 是工作表计算函数，不是访问控制机制。隐藏工作表、隐藏列、保护工作表或只在界面上显示部分查找结果，都不能可靠地限制他人访问工作簿中的数据。若某些记录不应被特定人员读取，应在数据源、文件权限或业务系统层面控制访问，而不是依靠公式不返回这些记录。

不要把查找公式当作审批、授权或身份验证手段。即使公式只显示通过条件筛选后的结果，底层数据、缓存内容或文件结构仍可能包含其他信息。共享文件前，应按实际安全要求移除不应共享的数据，并通过合适的权限机制交付。

此外，如果公式引用外部工作簿或链接到外部数据源，打开文件时可能会遇到更新链接的提示。只有在确认来源可信、确有业务需要时才刷新外部链接；对于来源不明的文件，不应因为查找结果不完整就盲目启用链接或内容。导入外部数据时，也应检查数据是否被当作公式执行。

如果把表格内容导出为 CSV，再交给其他程序打开，还要注意公式注入风险：以 =、+、- 或 @ 等字符开头的文本，在某些电子表格软件中可能被解释为公式。对不可信输入进行 CSV 导出时，应按接收软件和业务场景采用经过验证的中和或转义策略；不要把 CSV 当作能够天然隔离公式的纯文本安全格式。这类处理属于数据导出安全，与 VLOOKUP 或 XLOOKUP 的查找能力无关。

掌握两种函数的核心差异后，选择就不复杂：旧环境和简单的向右查找可以用 VLOOKUP；支持新函数、需要更灵活方向和参数时用 XLOOKUP。真正可靠的查找方案，还要同时考虑数据类型、重复项、排序、版本兼容和访问权限。