Excel XLOOKUP 函数 | 保姆级完全教程

📖 这是一篇专门为新手准备的 XLOOKUP 教程，看完就能学会！

一、XLOOKUP 到底是什么？为什么要学它？

1.1 核心作用

XLOOKUP = 查找界的"全能选手"

简单说，XLOOKUP 就是Excel里用来查找数据的函数。比如： - 根据员工工号，查找他的姓名 - 根据商品编号，查找对应的价格 - 根据班级姓名，查找他的成绩

1.2 为什么要用 XLOOKUP？

💡 划重点：XLOOKUP 是微软 2019 年推出的"超级查找函数"，目前 Excel 2021、Microsoft 365 版本可用。

二、语法参数一次搞懂

2.1 基础语法

=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时返回], [匹配模式], [搜索模式]) 

2.2 参数详解

2.3 匹配模式对照表

2.4 搜索模式对照表

三、从零开始！基础入门案例

3.1 案例一：最简单的查找

场景：根据工号查找姓名

     A列        B列 1   工号       姓名 2   A001       张三 3   A002       李四 4   A003       王五 

公式：

=XLOOKUP("A002", A2:A4, B2:B4) 

结果：返回 "李四"

解释： - 查找值："A002" - 查找数组：A2:A4（在工号列找） - 返回数组：B2:B4（返回姓名列）

3.2 案例二：用单元格引用查找

场景：在 F1 输入工号，自动显示姓名

     A列        B列       F列 1   工号       姓名       2   A001       张三       3   A002       李四      ← 在F1输入A002 4   A003       王五 

公式：

=XLOOKUP(F1, A2:A4, B2:B4) 

结果：返回 "李四"

💡 技巧：把查找值改成单元格引用，实现"输入即查询"！

3.3 案例三：跨列查找（向右查找）

场景：根据工号查找部门（部门在工号左边！VLOOKUP做不到）

     A列        B列        C列        D列 1   部门       工号       姓名 2   销售部     A001       张三 3   技术部     A002       李四 4   人事部     A003       王五 

公式：

=XLOOKUP("A002", B2:B4, A2:A4) 

结果：返回 "技术部"

🎉 重大突破：VLOOKUP 只能从左往右查，XLOOKUP 想查哪查哪！

四、精准匹配 vs 近似匹配，区别有多大？

4.1 精确匹配（最常用）

场景：根据分数查等级，必须精确对应

     A列        B列 1   分数       等级 2   0          不及格 3   60         及格 4   80         优秀 5   90         满分 

公式（找90分）：

=XLOOKUP(90, A2:A5, B2:B5, "未找到", 0) 

结果：返回 "满分"

参数 0 = 精确匹配，找不到就返回"未找到"

4.2 近似匹配（区间查找）

场景：根据分数自动判断等级

     A列        B列 1   最低分     等级 2   0          不及格 3   60         及格 4   80         优秀 5   90         满分 

公式（查85分）：

=XLOOKUP(85, A2:A5, B2:B5, "未找到", -1) 

结果：返回 "优秀"（85 落在 80-90 之间，找下一个最小的）

📌 口诀：精确匹配用 0，近似匹配用 -1 或 1

五、常见坑与解决办法

5.1 坑一：查不到，显示 #N/A

问题：公式返回 #N/A 错误

原因： - 查找的值在查找数组中不存在 - 查找数组范围写错了

解决：

=XLOOKUP("A004", A2:A4, B2:B4, "无数据") 

加上第4参数，找不到时显示"无数据"而不是错误

5.2 坑二：列位置变了，公式出错

问题：插入或删除列后，公式全部报错

原因：传统的 VLOOKUP 用列号，删列就坏

解决：

=XLOOKUP(F1, A:A, C:C, "无数据") 

用整列引用（A:A），插入列也不怕！

5.3 坑三：返回结果乱序/错误

问题：查到的数据和预期不一样

原因： - 查找数组和返回数组长度不一致 - 数据有空格或不可见字符

解决： 1. 确保两个数组行数相同 2. 用 TRIM() 去掉空格：

=XLOOKUP(TRIM(F1), TRIM(A2:A100), B2:B100) 

5.4 坑四：大小写不一致

问题：查找 "apple" 但数据是 "APPLE"

解决：统一转为小写

=XLOOKUP(LOWER(F1), LOWER(A2:A100), B2:B100) 

六、进阶实用技巧

6.1 技巧一：反向查找（从右往左查）

场景：根据姓名查工号（姓名在右，工号在左）

=XLOOKUP("张三", B2:B4, A2:A4) 

6.2 技巧二：返回多列数据

场景：根据工号，同时查姓名和部门

需要用 XLOOKUP 配合其他函数，或者使用辅助列：

姓名：=XLOOKUP($F$1, $A$2:$A$100, $B$2:$B$100) 部门：=XLOOKUP($F$1, $A$2:$A$100, $C$2:$C$100) 

6.3 技巧三：找不到时返回特定值

=XLOOKUP(F1, A2:A100, B2:B100, "⚠️未找到") 

6.4 技巧四：从后往前查找（最后一条记录）

场景：查找最近的交易记录

=XLOOKUP("A001", A2:A100, B2:B100, "未找到", 0, -1) 

参数 -1 = 从后往前查

6.5 技巧五：模糊匹配（带 * 的查找）

场景：只知道商品名前缀

=XLOOKUP("手机*", A2:A100, B2:B100, "未找到", 2) 

参数 2 = 通配符模式

七、实战案例：工资条查询系统

场景

     A列      B列      C列      D列 1   工号     姓名     部门     工资 2   001      张三     销售     8000 3   002      李四     技术     12000 4   003      王五     人事     9000 

在 F1 输入工号，自动显示完整信息：

八、XLOOKUP vs VLOOKUP 对比总结

九、常见问题 FAQ

Q1：我的Excel没有 XLOOKUP 怎么办？

A：检查版本，需要 Excel 2021 或 Microsoft 365。旧版本可以用 INDEX+MATCH 组合。

Q2：XLOOKUP 和大数据量会卡吗？

A：建议用整列引用（A:A）而不是几千行，性能更好。

Q3：找不到时不想显示任何内容怎么办？

A：第4参数用 ""（两个引号表示空文本）： excel =XLOOKUP(F1, A:A, B:B, "")

📝 练习题

2. 根据商品编号查找单价
4. 根据分数查等级（区间匹配）
6. 反向查找：姓名 → 工号

🎉 恭喜你！XLOOKUP 已学完！

如果觉得有用，点个赞 👍 转个发 📤 支持一下！

💡 关注我，获取更多 Excel 保姆级教程~