XLOOKUP 和 VLOOKUP 函数的区别与用法

VLOOKUP 可能是中文互联网上被搜索最多的 Excel 函数——「VLOOKUP 怎么用」「VLOOKUP 为什么出现 #N/A」「VLOOKUP 反向查找」几乎是每个职场新人的必经之路。它的几个「坑」：要手动数列号、只能向右查、默认是近似匹配，制造的错误表格恐怕比任何其他功能都多。XLOOKUP（Excel 2021 及 Microsoft 365 提供）把这些问题全部解决了。如果你的 Excel 有它，就用它。下面每个例子都会用两种写法各做一遍，你可以直观地看到差别。

示例数据

A1:C4 是一张产品表，另有一张订单表需要把单价填进去。

语法对比

=VLOOKUP(查找值, 数据表, 列序数, [匹配条件])
=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时], [匹配模式], [搜索模式])

核心区别就在形状上：VLOOKUP 要一个区域和一个列号；XLOOKUP 要在哪一列找和返回哪一列——两个范围，不用数列。

在中文版 Excel 里，函数名仍然是英文的 VLOOKUP 和 XLOOKUP，只是输入时的参数提示显示为中文，例如「lookup_value」会显示成「查找值」。

示例 1：精确匹配

查找 SKU「G-200」的单价。

VLOOKUP

=VLOOKUP("G-200", A2:C4, 3, FALSE)

XLOOKUP

=XLOOKUP("G-200", A2:A4, C2:C4)

两者都返回 100。注意 VLOOKUP 里的两处：3 是你数出来的列号；FALSE（也可以写 0）是精确匹配——如果省略，VLOOKUP 会在未排序的数据上做近似匹配，返回它「碰到」的某个值，这正是无数隐性错误的来源。XLOOKUP 默认就是精确匹配。

示例 2：反向查找（从右往左查）

查找产品名为「充电宝」的 SKU。答案在查找列的左边。

VLOOKUP：做不到。查找列必须是区域的第一列。常见的变通写法是 INDEX/MATCH：

=INDEX(A2:A4, MATCH("充电宝", B2:B4, 0))

网上流传的另一种写法是 VLOOKUP 配合 IF({1,0}, ...) 构造数组，能用但难读，不推荐。

XLOOKUP

=XLOOKUP("充电宝", B2:B4, A2:A4)

和示例 1 完全同一个结构，方向无关紧要。

示例 3：找不到时显示什么

查找一个不存在的 SKU。

VLOOKUP 返回 #N/A。要显示得友好一些，得在外面套一层：

=IFERROR(VLOOKUP("X-999", A2:C4, 3, FALSE), "未找到")

（IFERROR 会把所有错误都藏起来，包括范围写错这种真正的错误——这也是审计人员不喜欢它的原因。更稳妥的是 IFNA，只处理 #N/A。）

XLOOKUP 自带第四个参数：

=XLOOKUP("X-999", A2:A4, C2:C4, "未找到")

只有真正找不到时才显示「未找到」，其他错误照常暴露。

示例 4：插入一列，其中一个就错了

在「产品」和「单价」之间插入一列「类别」。

VLOOKUP 的列号仍然是 3，现在返回的是类别而不是单价，而且不报任何错。所有指向这张表的 VLOOKUP 都悄悄错了。

XLOOKUP 引用的是 C2:C4 这个范围，Excel 会自动把它调整为 D2:D4。结果依然正确。

示例 5：多条件查找

从一张「区域、产品、单价」的表里，查找「华东」区域「保温杯」的单价。

VLOOKUP 需要先加一个辅助列，把区域和产品拼接起来（如 =A2&B2），再去查这个辅助列。

XLOOKUP 可以直接在公式里拼接：

=XLOOKUP("华东"&"保温杯", A2:A50&B2:B50, C2:C50)

范围之间的 & 会即时生成一组组合键。

示例 6：一次返回多列

VLOOKUP：一列一个公式。

XLOOKUP：返回一个多列范围，结果会自动「溢出」到相邻单元格：

=XLOOKUP("G-200", A2:A4, B2:C4)

→ 蓝牙耳机 | 100.00，占两个相邻单元格。

示例 7：查找最后一条记录，以及正确的区间查找

查找某客户最近一次的订单（数据按时间从早到晚排列）：

=XLOOKUP(客户, A:A, D:D, , 0, -1)

搜索模式 -1 表示从下往上找。VLOOKUP 没有等价的写法。

按收入查找提成比例（找出小于等于收入的最大门槛）：

=XLOOKUP(收入, 门槛, 比例, , -1)

匹配模式 -1 表示「精确匹配，否则取下一个较小值」。这正是 VLOOKUP 第四个参数写 TRUE 时的用途，但 XLOOKUP 不要求门槛列事先排序。

#N/A 报错的五个常见原因

不管用哪个函数，查找结果出现 #N/A 时，按下面的顺序排查，基本都能找到原因：

1. 数字和文本格式不一致。 一边是数字 10001，另一边是从系统导出的文本型 "10001"，看起来一样，其实匹配不上。统一格式：用 数据 → 分列 → 完成 把文本转成数字，或者在公式里写 --B2 / B2&"" 进行转换。
2. 多余的空格或不可见字符。 从网页或系统复制来的数据常带尾部空格。用 TRIM 和 CLEAN 清理后再查。
3. 全角和半角字符混用。 G-200 和 G－200（全角横线）在 Excel 看来是两个不同的值。中文输入法下尤其容易出现，可以用 ASC 函数把全角转成半角。
4. 查找区域没有锁定。 公式往下拖时，A2:C4 会变成 A3:C5、A4:C6……要写成 $A$2:$C$4，或者直接引用整列。
5. 确实不存在。 这时候 #N/A 是正确的结果，用 XLOOKUP 的第四个参数或 IFNA 给出友好的提示。

实战：给订单表批量匹配单价

回到开头的场景：「订单」工作表有 2,000 多行，B 列是 SKU、C 列是数量，需要从「产品」工作表把单价匹配过来，再算出每笔订单的销售额。

用 VLOOKUP： 在订单表 D2 输入

=IFERROR(VLOOKUP(B2, 产品!$A:$C, 3, FALSE), "未找到")

再在 E2 输入 =IF(ISNUMBER(D2), D2*C2, "")，双击填充柄向下填充。

用 XLOOKUP： 一步到位

=XLOOKUP(B2, 产品!A:A, 产品!C:C, "未找到") * C2

如果想把「未找到」的行单独列出来，再用 =FILTER(订单!A2:C2200, ISNA(XMATCH(订单!B2:B2200, 产品!A:A))) 得到一份匹配失败的清单——这些通常是新上架还没录入产品表的 SKU，或者 SKU 里带了空格。

两种做法的最终结果相同，但 XLOOKUP 版本少一个辅助列，也不会因为有人在产品表里插入一列而悄悄出错。批量匹配完成后，建议用 COUNTIF 数一下「未找到」的数量，和你的预期对一对。

报错对照表

WPS 表格支持 XLOOKUP 吗

较新版本的 WPS 表格已经支持 XLOOKUP，以及 FILTER、UNIQUE 等动态数组函数；旧版本的 WPS 可能没有，输入后会显示 #NAME?。如果你的文件要发给使用旧版 Excel（2019 及更早）或旧版 WPS 的同事，稳妥起见仍然用 VLOOKUP 或 INDEX/MATCH。判断方法很简单：在单元格里输入 =XLO，看函数提示列表里有没有 XLOOKUP。

三种写法速查

同一个需求「按 SKU 查单价」，三种写法放在一起方便对照：

如果你不确定同事用的是什么版本，选 INDEX/MATCH：它不怕插入列，能反向查找，而且所有版本的 Excel 和 WPS 都能打开。

还要不要学 VLOOKUP

要学，至少要看得懂——数以百万计的现有工作簿里都是它；当文件必须在 Excel 2019 或更早版本中打开时，你也只能写它。对于 Microsoft 365 里的新工作，XLOOKUP 更短、更安全，而且不会因为插入一列就出错。更多常用函数见 数据分析常用的 Excel 公式。

捷径：不写公式也能「查找」

如果只是一次性的问题——「G-200 的单价是多少？」「哪些客户买过充电宝？」——你并不需要公式。把包含两张表的工作簿上传到 ChatExcel 直接问就行。多工作表的工作簿会逐个读取，所以 「把产品表的单价匹配到每笔订单，再按区域汇总」 不写任何查找公式就能完成；你也可以把两个独立的文件加到同一个对话里一起查。如果需要一个已经合并好单价列的新文件，可以让它直接生成 Excel 供你下载。当你需要把公式留在文件里时，问一句 「在 Excel 里怎么写？」，就能得到上面那样、用你真实列号写好的 XLOOKUP。ChatExcel 为付费订阅，套餐见 价格页。