excel函数xlookup用法，和vlookup区别在哪

上周五下午快下班，同事丢来一张两千多行的报价表，让我按物料编码把单价填回去。我第一反应是用 excel函数xlookup 一把梭，结果手一抖插了列，早先那版 vlookup 公式整片变成 #REF!，白折腾二十分钟。索性推倒重来，顺手把这次的用法记下来。读者里做报表的多，估计你也遇到过。

老实说，我以前对 excel函数xlookup 有点偏见，觉得新函数是花架子，vlookup 用了七八年也没出过大事。真上手才发现，省的不是一点半点，尤其是表宽、列多的时候。

查找表到底是怎么被查到的

先说清楚一张查找表长什么样：左边一列是键，右边若干列是值，中间靠行号把两边对上。你脑子里有这张图，参数就不会填乱。

vlookup 的做法是从你给的第一列往下扫，找到键以后记下第几行，再去数列序号。列号是写死的，所以一插列它就数错位，返回隔壁那列的数，还不报错。表头一改，你自己都记不清当时数到第几列。

excel函数xlookup 换了个思路：键列和返回列分开告诉它，函数内部按索引定位，不再依赖列序号。这就是它插列不翻车的根本原因。

我当时以为这不过是语法糖，结果发现索引这件事很关键。表越宽，vlookup 要数的列越多，错一位你根本看不出来，这种错最难查。

顺手提一句，这个索引是逻辑上的，不用你手动建。只要保证查找区域是一行或一列、返回区域和它对得上行数，剩下的交给函数就行。

区域给错是另一个高频坑。lookup_array 只能是一行或一列，把整张二维表框进去它不认，这条我在下面报错那节细说。

我现在的习惯是把键列单独抽成一张小表，只放编码和单价两列，查找范围和返回范围都短，公式一眼能看懂。

excel函数xlookup 和 vlookup 的区别

最直观的区别是方向。vlookup 只能往右返回，键必须压在最左列；excel函数xlookup 左右都行，返回列在键列左边照样取得到。

第二个区别是默认匹配方式。vlookup 第四个参数不写，默认走近似匹配，很多人以为不写就是精确匹配，结果查出一堆不相干的数。excel函数xlookup 默认就是精确匹配，这点省心。

第三个是查不到时的处理。vlookup 给 #N/A，你还得在外面套 IFERROR；excel函数xlookup 有现成的 if_not_found 参数，第四个位置直接写兜底值。

第四个是反向查找。键有重复时，vlookup 永远返回第一条；excel函数xlookup 把 search_mode 设成 -1，就从最后一条往前找，取最新的记录。

至于版本，excel函数xlookup 是随 Microsoft 365 和 Excel 2021 之后一起提供的，Excel 2019 及更早的版本没有它，具体可用范围以官方文档为准。WPS 新版也能用，但我不会拿它去赌别人的电脑。

xlookup 的几个参数分别填什么

我的习惯是分两步：第一步把键列和返回列框准，第二步再补后面三个可选参数。顺序反了容易乱，这一步我吃过亏。下面这条填在 B2 单元格：

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "未找到", 0, 1)

A2 是要查的物料编码，Sheet2!A:A 是键列，Sheet2!C:C 是单价列。第四个 "未找到" 是查不到时的兜底，第五个 0 表示精确匹配，最后一个 1 是从前往后找。

后两个可选参数别背串。match_mode 管匹配方式：0 精确，-1 精确或下一个较小项，1 精确或下一个较大项，2 支持通配符。search_mode 管方向：1 从前往后，-1 从后往前，2 和 -2 是二分查找，要求数据已排序。

老实说二分查找我基本不用，除非表特别大又排好了序。日常填 0 和 1 就够，参数塞太满反而容易写错。

你看，前三个必填，后面三个不写也有默认值。真正要记的只有 match_mode 那几个取值，用两次就熟了。

xlookup 查不到时报错怎么处理

最常见的报错是 #N/A，意思是查找区域里确实没有这个键。先别急着改公式，回去确认两件事：键的格式是否一致，前后有没有多余空格。

这个坑我踩过：一边是文本型数字，一边是数值，肉眼看着一模一样，函数就是不认。用 TRIM 清空格、用 VALUE 转数值，问题立刻消失。

如果数据本身没问题，只是某些记录本来就没有，那就用 if_not_found 兜底。下面这行的意思是查不到返回空字符串，别让整列飘红，同样是填在 B2。

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "", 0, 1)

注意第四个参数给的是 ""，不是空格。给空格看着是空的，其实里面藏了一个字符，后面拿去求和会出错。

老版本没有 if_not_found 的，退回 IFERROR 包一层：=IFERROR(原公式, "")。效果差不多，只是多套一对括号，可读性差点。

还有一种 #VALUE!，多半是 lookup_array 和 return_array 行数不一致，或者你给了二维区域。把区域收成一列、两张表行数对齐再试。

更隐蔽的是明明有记录，返回的却是隔壁列。那是返回区域整体偏移了，复制公式时列没锁，加个 $ 就好。

什么时候我还是会切回 vlookup

说实话，给别人改表时我还是用 vlookup。不是它好，是对方环境不一定支持新函数，公式发过去一片 #NAME?，来回解释太折腾。

自己维护的表，我一律用 excel函数xlookup。多打几个字符，换来插列不炸、查不到可控、反向往回取，这笔账怎么算都值。

还有一点，公式里的相对引用和绝对引用要分清，拖拽之前先把区域锁死，能省掉一半返工。先锁区域、再补兜底、最后才动匹配方式，这个顺序我试过错，比记一堆参数管用。