Excel 2021用户必看!XLOOKUP这个新函数,好用到让你忘记VLOOKUP!
- 2026-11-04 23:20:21
大家好，我是匿玄机，你的Excel搭子。
今天要跟你聊一个Excel查找函数里的「终极武器」——XLOOKUP。如果你是Excel 2021或Office 365用户，却还在用VLOOKUP苦苦挣扎，那么这篇文章将是你彻底告别查找烦恼的转折点。
先来回忆一下VLOOKUP让你抓狂的那些瞬间：
- 只能从左向右查
：想通过姓名找工号？对不起，请先把工号列挪到姓名列右边 - 列号数到头秃
：数据表有20列，想返回第15列的值？1,2,3… 15，万一中间插入新列，公式直接崩掉 - 错误处理要嵌套
：找不到数据时显示#N/A，想换成「未找到」？必须再套一层IFERROR
如果你对以上场景深有共鸣，那么XLOOKUP就是为你量身定制的解决方案。
一、XLOOKUP：为什么它是查找函数的「完全体」？
XLOOKUP是微软在Excel 2021/365中推出的全新查找函数，它彻底解决了VLOOKUP、HLOOKUP甚至INDEX+MATCH组合的所有痛点，用一条简洁语法实现了全方位查找能力。
核心语法：=XLOOKUP(查找值, 查找数组, 返回数组, [如果找不到], [匹配模式], [搜索模式])
💡 革命性优势：1. 双向查找：查找列和返回列可以任意位置，左右随便查2. 默认精确匹配：再也不用担心忘记写FALSE而得到错误结果3. 内置错误处理：第4参数直接写友好提示，告别IFERROR嵌套4. 支持通配符：模糊查找一键实现5. 动态数组：结果自动溢出，无需拖动填充
二、三大实战技巧：从商品到员工，全面覆盖职场需求
技巧1：商品价格查询——告别VLOOKUP的列号噩梦
场景：你负责公司电商平台的库存管理，每天需要根据商品编号快速查询最新价格。数据表有20多列，商品编号在A列，价格在H列。
传统VLOOKUP做法：=VLOOKUP(F2, A:H, 8, FALSE)
致命问题：如果你在价格列前插入新列（如「折扣率」），列号8就变成了9，公式必须手动修改，否则返回错误数据。
XLOOKUP解决方案：
=XLOOKUP(F2, A:A, H:H, "商品未找到")公式解析：
- F2
：要查询的商品编号 - A:A
：在A列中查找这个编号 - H:H
：找到后，从H列返回对应的价格 - "商品未找到"
：如果A列没有这个编号，就显示这个友好提示
核心优势：1. 无需数列号，直接引用「价格列」2. 插删列不影响公式逻辑3. 内置错误处理，用户体验更佳
图1：商品价格查询表示例（商品编号、名称、价格等）
技巧2：员工信息精准匹配——双向查找一键实现
场景：作为HR，你经常需要在员工档案中双向查询——既要用工号查姓名，也要用姓名查工号。传统方法需要两个不同的VLOOKUP公式，或者复杂的INDEX+MATCH组合。
VLOOKUP的尴尬：想通过姓名查工号？对不起，VLOOKUP只能从左向右查，你必须把姓名列挪到工号列右侧，或者使用INDEX+MATCH。
XLOOKUP的优雅：
=XLOOKUP(H2, B:B, A:A, "员工不存在") # 通过姓名查工号 =XLOOKUP(H3, A:A, B:B, "工号不存在") # 通过工号查姓名公式解析：第一个公式：在B列（姓名）中查找H2的值，找到后从A列（工号）返回对应结果。第二个公式：在A列（工号）中查找H3的值，找到后从B列（姓名）返回对应结果。
革命性突破：XLOOKUP彻底打破了「查找方向」的限制，你可以自由指定查找列和返回列，无论它们在数据表中的相对位置如何。
图2：员工信息表示例（工号、姓名、部门等）
技巧3：错误值灵活处理——告别#N/A的困扰
场景：你在处理销售报表时，经常需要查找客户的最新订单金额。但有些客户可能近期没有下单，传统查找公式会返回#N/A，导致整个报表看起来「满是错误」。
传统解决方案：使用IFERROR包裹VLOOKUP：=IFERROR(VLOOKUP(F2, A:D, 4, FALSE), "暂无订单")
问题：公式变得冗长，可读性下降，而且每次都要嵌套IFERROR。
XLOOKUP的简洁方案：
=XLOOKUP(F2, A:A, D:D, "暂无订单", 0, -1)双重优势：
- 内置错误处理
：第4参数直接设置「未找到」时的提示文本 - 搜索模式灵活
：第6参数设为-1，实现「从下往上」搜索，直接找到最后一次匹配记录
这个公式不仅优雅处理了「找不到数据」的情况，还自动返回最新记录，一举两得。
图3：销售订单表示例（客户、日期、金额等）
三、常见错误避坑指南
即使XLOOKUP如此强大，使用时仍需注意以下细节：
- 版本兼容性
：XLOOKUP是Excel 2021/365专属函数。如果文件需要分享给使用旧版Excel的同事，需提前确认或提供替代方案（如INDEX+MATCH）。 - 数据范围一致性
：查找数组和返回数组必须具有相同的行数或列数，否则会返回#VALUE!错误。 - 通配符模式
：使用通配符（*、?）查找时，必须将匹配模式参数设为2，否则通配符会被当作普通字符处理。 - 性能优化
：对于超过10万行的大数据集，建议避免使用通配符模式，改用精确匹配或二分搜索模式（参数2或-2），提升计算速度。
💡 最佳实践：1. 使用绝对引用（$A$2:$A$100）锁定数据范围，防止公式拖动时范围偏移2. 为查找值添加TRIM函数去除前后空格：=XLOOKUP(TRIM(F2), A:A, D:D)3. 结合LET函数提高复杂公式的可读性：=LET(lk, F2, lkArr, A:A, retArr, D:D, XLOOKUP(lk, lkArr, retArr))
四、总结与行动
XLOOKUP的出现，标志着Excel查找函数进入了「智能化」时代。它用一条简洁公式，解决了VLOOKUP的所有痛点：
从今天起，请将XLOOKUP作为你的「首选查找函数」。它不仅能让你的公式更简洁、更稳定，还能让你在处理复杂查找需求时游刃有余。
互动话题
你在工作中最常使用哪种查找函数？VLOOKUP、INDEX+MATCH还是已经用上了XLOOKUP？
欢迎在评论区留言分享你的使用心得，或者提出你在查找数据时遇到的具体难题！
明日预告
下一期我们将进行「首周复盘与下一周选题规划」，总结首周7个公式的学习成果，并为你揭晓下一周的精彩学习路径！
明天同一时间，我们不见不散！
