XLOOKUP：一次写对的全能查找

VLOOKUP 大概是全世界用得最多的 Excel 函数，也是被吐槽最多的函数。它的三个硬伤几乎人人都撞过：第一，查找值必须在查找区域的第一列，想反过来从右往左查就得把列顺序调整一遍，或者套一层 CHOOSE 硬凑；第二，第三个参数写的是列序号，一旦在中间插入了一列，所有公式的列号全部错位，而且 Excel 不会报错，只会默默返回错的字段；第三，找不到时返回 #N/A，想给个友好的提示必须外面再套一层 IFERROR。XLOOKUP 就是冲着这三个痛点来的，它把查找区域和结果区域彻底分开、不需要数列号、自带找不到时的返回值，还顺手支持反向查找、近似匹配、二分查找和通配符。

对已经会 VLOOKUP 的人来说，换到 XLOOKUP 的学习成本几乎为零，因为核心思路完全一致：给一个查找值，在某一列里找，找到之后返回同一行另一列的值。区别只在于参数的位置和含义更合理。本篇会先讲清楚它到底改进了什么，再给可以直接复制的写法，最后把几个容易踩的坑和版本兼容问题说透。学会了它，本书后面几篇讲的引用类函数才能真正串起来用。

一、它到底改进了什么

第一个改进是查找列和结果列分离。VLOOKUP 要求你给一个大表，然后数出要返回第几列；XLOOKUP 让你分别给两片区域——lookup_array 是去哪儿找，return_array 是找到之后取什么。这两片区域可以完全不相干，甚至可以在不同的工作表上。于是“从右往左查”这个 VLOOKUP 时代需要绕一大圈的问题，在 XLOOKUP 里根本不存在，写法和从左往右查一模一样。

第二个改进是不会因为插入列而错位。因为结果区域是直接引用的，你在源表中间插入十列，公式引用的还是那一片区域，结果不变。这一条看似小事，实际能省掉大量的返工：过去但凡有人往对照表里插一列，全公司的模板就得集体复查一遍。这也是为什么很多企业在升级到 Microsoft 365 之后，第一件事就是把核心模板里的 VLOOKUP 全换成 XLOOKUP。

第三个改进是自带“找不到时的默认值”。VLOOKUP 找不到就返回 #N/A，想要“未找到”三个字必须写成 =IFERROR(VLOOKUP(...), "未找到")，而 IFERROR 会连别的错误一起吞掉，排查问题时很麻烦。XLOOKUP 的第四个参数专门干这件事，写成 =XLOOKUP(A2, B:B, D:D, "未找到") 就行，别的错误该报还是报，不会被掩盖。

第四个改进是默认精确匹配。VLOOKUP 的第四个参数省略时默认是 TRUE，也就是近似匹配，这意味着新手写 =VLOOKUP(A2,B:C,2) 时如果没排序，结果就是错的，而且 Excel 一声不吭。XLOOKUP 默认就是精确匹配，想用近似匹配必须显式写第五个参数。这一个默认值的变化，消灭了 VLOOKUP 时代最高频的一类错误。此外它还支持从后往前找（第六个参数写 -1），适合“取最后一次记录”这类需求，VLOOKUP 完全做不到。

二、语法：六个参数

XLOOKUP 的完整语法写在下面的代码块里，日常只用到前四个。

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 表示二分查找（要求已排序，速度快）。

第五个参数的 -1 和 1 就是近似匹配，它们替代了过去 VLOOKUP 做区间查找的玩法。比如有一张税率表，第一行是收入下限、第二行是税率，想根据收入查税率，写成 =XLOOKUP(收入, 下限区域, 税率区域, , -1) 即可，意思是“找精确值，找不到就取不大于它的最大值”。注意使用这两个模式时，查找列必须按升序排列，否则结果不可预期，这一点和 VLOOKUP 的要求是一样的。

第六个参数里的 -1 特别有用。假设一张表里同一个客户有多条记录，你想取“最后一次下单的金额”，写成 =XLOOKUP(H1, B2:B500, D2:D500, , 0, -1) 就会从最后一行往前找，返回最后一条匹配记录。这个需求用 VLOOKUP 只能靠辅助列或者数组公式实现，XLOOKUP 一个参数搞定。二分查找模式（2 和 -2）在处理几万行时能明显提速，但要求数据严格排序，日常用得不多。

理解参数顺序有个小窍门，记住一句话就够了：找什么、去哪儿找、返回什么、找不到怎么办、怎么算匹配上、从哪头开始找。这六个问题正好对应六个参数，写公式时按这个顺序在心里问一遍，参数位置基本不会错。比起 VLOOKUP 那种“查找值、大表、第几列、要不要近似”的设计，XLOOKUP 的参数顺序更贴近人的思考方式，这也是它写起来不容易出错的原因之一。

三、五个能直接抄的写法

下表列出了五种最常见的查找需求，公式可以直接复制，把区域换成你自己的即可。第一行是基础款，第二行和第五行是最能体现 XLOOKUP 优势的两个。

第三行值得展开：如果 return_array 给了多列，XLOOKUP 会返回一个横向的数组并自动溢出。比如 =XLOOKUP(H1, B2:B500, B2:E500) 会一次返回该客户对应的四列信息（客户名、城市、金额、销售员），不需要写四条公式。这个特性在做“查询卡片”时极其好用：输入一个编号，右边自动列出这个编号对应的全部字段。

还有一个高频写法是横向查找。过去横向查找要用 HLOOKUP，现在统一用 XLOOKUP 就行，因为查找区域和结果区域都可以是横向的：=XLOOKUP("二月", B1:M1, B2:M2) 在第一行里找“二月”，返回第二行对应位置的值。写法和纵向查找完全一致，不需要记两套函数。这也是 XLOOKUP 被称为“全能查找”的原因——它同时替代了 VLOOKUP 和 HLOOKUP。

如果需要按多个条件查找，最常见做法是建一个辅助列把几个字段拼起来，或者用布尔运算构造：写成 =XLOOKUP(1, (B2:B500=H1)*(C2:C500=H2), D2:D500, "无") 就能同时匹配两个条件。原理和 FILTER 里讲的一样，两个条件相乘得到一串 0 和 1，再去找等于 1 的位置。这四种解法各有优劣，本书 多条件查找的四种解法对比 里有专门的横向对比。

四、真实场景

第一个场景是做查询模板。在 H1 留一个输入框（或者下拉菜单），下面写 =XLOOKUP(H1, 员工表!A:A, 员工表!B:E, "查无此人")，一次返回姓名、部门、职级、入职日期四列。把这片区域配上边框和标题行，就做成了一个免维护的查询卡片。员工表增加人员时查询自动覆盖，插入列也不会错位，比过去用 VLOOKUP 写四条公式清爽得多，而且不用数列号。

第二个场景是替换掉模板里所有带列号的 VLOOKUP。改造方法很机械：把原来的 =VLOOKUP(A2,Sheet2!$A:$D,3,FALSE) 拆成查找列 Sheet2!$A:$A 和结果列 Sheet2!$C:$C，写成 =XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$C:$C, "未找到")。如果有 IFERROR 包着，就把 IFERROR 去掉，把“未找到”挪进第四个参数。整张表改完通常只需要十几分钟，之后就再也不用担心插入列的问题。

第三个场景是取最新记录。前面说过第六个参数写 -1 可以从后往前找，这在“客户档案表按时间追加记录”的场景里非常实用：同一个客户每个月的额度都在表里占一行，你要的是最新那条，写成 =XLOOKUP(H1, B2:B5000, D2:D5000, , 0, -1) 就永远拿到最后一行。前提是源数据确实是按时间顺序追加的，如果顺序打乱了就要改用排序函数先排好。

第四个场景是批量查找。XLOOKUP 的第一个参数可以是一片区域，写成 =XLOOKUP(A2:A100, 对照表!B:B, 对照表!D:D, "未找到") 会一次返回一百个结果并自动溢出，不需要向下填充。这个用法在做批量匹配时特别省事，而且比填充公式更不容易出现“某一行忘了拖”的问题。如果源数据是超级表，行数变化时结果区域也跟着变。

第五个场景是和其他函数组合。比如先查后筛：=FILTER(订单表!A2:F5000, 订单表!C2:C5000=XLOOKUP(H1, 员工表!A:A, 员工表!C:C, ""), "无")，先根据工号查出所属大区，再用大区去筛订单明细。又比如查完之后再做计算：=SUM(XLOOKUP(H1, B:B, D:F)) 对返回的多列结果求和。这种把 XLOOKUP 当作“取值工具”嵌在更大公式里的用法，是进阶的标志。相关思路在 FILTER：按条件把行整条捞出来 里也有涉及。

第六个场景是跨表核对。财务对账时常常要拿两张表的编号做匹配，找出哪些只在一张表里存在。做法是在一张表旁边写 =XLOOKUP(A2, 另一表!A:A, 另一表!A:A, "缺失")，如果返回的是原值说明两边都有，返回“缺失”说明这张表里有而另一张没有；反过来再做一遍就能找出另一张独有的记录。比用 COUNTIF 更直观的是，它顺便还能返回对方表里对应的金额，直接做差额比较。这类“双向核对”的工作，用 XLOOKUP 比用透视表或者条件格式都要省事。

五、翻车提醒与版本要求

第一，两片区域的大小必须一致。lookup_array 和 return_array 的行数（横向查找时是列数）不同会直接返回 #VALUE!。这是 XLOOKUP 最常见的使用错误，尤其是复制公式时只改了一边。写完后扫一眼两边的行号是否相同，是个值得养成的习惯。

第二，查找列里有重复值时只返回第一个（或者从后往前找时的最后一个）。XLOOKUP 不会警告你“找到了多个”，它默默返回一个。如果业务上要求“必须唯一”，建议先用 COUNTIF 检查重复，或者在结果旁边加一列校验公式。要返回所有匹配项而非第一个，应该用 FILTER 而不是 XLOOKUP。

第三，前导后导空格和数据类型不一致会导致查不到。"1001" 和 1001 不一样，一个带空格的客户名和干净的客户名不一样。如果源数据是系统导出的，稳妥做法是写成 =XLOOKUP(TRIM(A2)&"", TRIM(B:B)&"", D:D, "未找到") 统一成文本处理，或者用 VALUE 统一成数字。这类问题的排查办法是用 LEN 检查长度、用 ISTEXT 检查类型。

第四，整列引用会影响性能。=XLOOKUP(A2, B:B, D:D) 会扫描一百多万行，一两个公式无所谓，几百个就会明显卡。建议把区域写死成 B2:B5000，或者转成超级表用结构化引用。

第五，if_not_found 参数省略时找不到会显示 #N/A。做模板时建议永远写上，避免报表上出现一片错误值。反过来，如果你希望“找不到时返回空”，就写空字符串而不是什么都不写，这两者显示效果完全不同。

版本方面，XLOOKUP 需要 Microsoft 365 订阅版或者 Excel 2021 及以后版本。Excel 2019、2016 及更早版本都没有，公式会显示为 #NAME?。需要说明的是，XLOOKUP 比动态数组函数稍晚一点发布，但都属于 Microsoft 365 的功能范围，能用 UNIQUE 的环境基本都能用 XLOOKUP。老版本里替代方案仍然是 VLOOKUP、INDEX 加 MATCH 的组合，以及 HLOOKUP。其中 INDEX 加 MATCH 的组合功能上最接近 XLOOKUP，写法是 =IFERROR(INDEX(D:D, MATCH(A2, B:B, 0)), "未找到")，支持反向查找、不会因为插入列错位，只是写起来长一些、不支持多列返回。本书 CHOOSE 和 INDEX：不写 IF 也能分支 会详细讲 INDEX 的用法。

常见问题

XLOOKUP 查找速度比 VLOOKUP 快吗？

在几百行的表上感觉不出差别，在几万行以上会有区别。XLOOKUP 默认采用线性搜索，和 VLOOKUP 的精确匹配差不多；但它的第六个参数支持二分查找模式（写 2 或 -2），在已排序的大表上能把查找次数从几万次降到十几次，提速非常明显。使用二分查找的前提是查找列严格升序排列，否则结果不可预期。另一个影响速度的因素是区域大小，把整列引用改成实际数据区域，往往比换函数带来的提升更大。

XLOOKUP 能不能一次返回多列？

可以，把第三个参数写成多列的区域即可。=XLOOKUP(H1, B2:B500, B2:E500, "未找到") 会返回该行对应的四列内容，并自动横向溢出到右边的单元格。注意返回区域的列数决定结果占几列，右边要留出足够的空地，否则会报 #SPILL!。如果想改成纵向显示，外面套一层 TRANSPOSE：=TRANSPOSE(XLOOKUP(H1, B2:B500, B2:E500))。这个特性在做查询卡片时最省事，一次写一条公式替代过去的四条。

查找不到的时候为什么显示 #N/A 而不是我写的提示？

检查第四个参数有没有写对位置。=XLOOKUP(A2, B:B, D:D, "未找到") 里“未找到”必须是第四个参数；如果中间漏写了参数，比如写成 =XLOOKUP(A2, B:B, D:D, , "未找到")，那“未找到”就跑到了第五个参数（匹配方式）的位置上，Excel 会报错或者行为异常。另外确认一下是不是真的找不到——很多时候查不到的原因是查找值带了空格或者类型不一致，加一层 TRIM 或者统一类型就能解决。

我想做区间查找，比如按收入查税率，该怎么写？

用第五个参数。=XLOOKUP(收入, 下限区域, 税率区域, , -1) 表示精确匹配，找不到就取不大于查找值的最大项，也就是常见的区间查找。如果规则是“取不小于查找值的最小项”，写成 =XLOOKUP(收入, 下限区域, 税率区域, , 1)。两种模式都要求查找列按升序排列，乱序时结果不可预期。做税率表、折扣表、等级评定这类区间映射时，这是最标准的写法，比嵌套一堆 IF 清爽得多，改规则时只改对照表即可。

XLOOKUP 和 VLOOKUP 能混着用吗？旧文件要不要全改？

技术上可以混用，但建议逐步统一。判断要不要改的标准是：这份模板还会用多久、会不会有人往源表里插入列。一次性的分析文件不用改；要长期维护、多人协作的核心模板，值得花十几分钟换成 XLOOKUP，收益是以后再也不用担心列错位。混用期间要注意，VLOOKUP 的近似匹配默认值为 TRUE，而 XLOOKUP 默认是精确匹配，替换时别把行为改错了。改造完成后用几组已知结果的数据做一次抽样核对，确保没有批量出错。

多个条件同时匹配，XLOOKUP 该怎么写？

最简洁的写法是布尔乘法：=XLOOKUP(1, (B2:B500=H1)*(C2:C500=H2), D2:D500, "无")。两个条件分别产生一串 TRUE 和 FALSE，相乘后只有同时满足的位置是 1，XLOOKUP 去找这个 1 即可。三个条件就再乘一项。另一种做法是在源表加一个辅助列，把几个字段用分隔符拼起来当唯一键，两边都拼同样的键再去查，好处是可读性好、能配合二分查找提速。两种做法各有适用场景，具体对比在 多条件查找的四种解法对比 里有详细说明。