Excel中xlookup使用教程(豆包AI)
摘要: Excel中xlookup使用教程(豆包AI)
一 xlookup 教程
XLOOKUP 是 Excel 365 和 Excel 2021 中引入的强大查找函数,相比 VLOOKUP 更加灵活和易用。下面是一份详细的 XLOOKUP 教程:
XLOOKUP 基本语法
1 | XLOOKUP(查找值, 查找范围, 返回范围, [未找到时的返回值], [匹配模式], [搜索模式]) |
- 查找值:要搜索的值(必填)
- 查找范围:要搜索的区域(必填)
- 返回范围:要返回结果的区域(必填)
- [未找到时的返回值]:可选,查找失败时返回的内容(默认返回 #N/A)
- [匹配模式]:可选,指定匹配方式(0 = 精确匹配,-1 = 精确匹配或下一个较小项,1 = 精确匹配或下一个较大项,2 = 通配符匹配)
- [搜索模式]:可选,指定搜索方向(1 = 从第一个到最后一个,-1 = 从最后一个到第一个)
XLOOKUP 与 VLOOKUP 的核心区别
- 双向查找:无需使用 COLUMN 函数即可向左查找
- 动态数组支持:可返回多个结果
- 更简单的语法:无需需指定列索引
- 灵活的匹配模式:内置通配符和近似匹配
- 反向搜索:可从后向前查找
实用示例
基础精确匹配(替代 VLOOKUP)
需求:根据 A 列的姓名,查找 B 列的年龄
1 | =XLOOKUP("张三", A2:A10, B2:B10) |
向左查找(VLOOKUP 难以实现)
需求:根据 C 列的工号,查找 A 列的姓名
1 | =XLOOKUP("001", C2:C10, A2:A10) |
多条件查找
需求:根据姓名(A 列)和部门(B 列),查找工资(C 列)
1 | =XLOOKUP(1, (A2:A10="张三")*(B2:B10="技术部"), C2:C10) |
近似匹配(适合区间查找)
需求:根据分数(A 列)查找等级(B 列)
1 | =XLOOKUP(85, A2:A5, B2:B5, "无结果", 1) |
注:A 列需按升序排序(如 60,70,80,90)
未找到时自定义返回值
1 | =XLOOKUP("李四", A2:A10, B2:B10, "该用户不存在") |
反向搜索(查找最后一个匹配项)
需求:在 A 列中查找最后一个出现的 “苹果”
1 | =XLOOKUP("苹果", A2:A10, B2:B10, , 0, -1) |
常见问题
- #SPILL! 错误:返回范围大小与查找范围不匹配
- #N/A 错误:未找到匹配值且未设置自定义返回值
- 不支持旧版本 Excel:仅 Excel 365/2021 及以上版本可用
通过 XLOOKUP,大多数查找场景都能更简洁地实现,建议逐步用它替代 VLOOKUP 和 HLOOKUP。
二 XLOOKUP Demo
以下是一个 XLOOKUP 函数的实际应用示例,帮助你直观理解它的用法。
假设我们有一份员工信息表,包含姓名、部门、职位和工资四列数据:
| 姓名 | 部门 | 职位 | 工资 |
|---|---|---|---|
| 张三 | 技术部 | 工程师 | 8000 |
| 李四 | 市场部 | 专员 | 6500 |
| 王五 | 技术部 | 经理 | 12000 |
| 赵六 | 人事部 | 主管 | 7500 |
| 孙七 | 市场部 | 经理 | 9000 |
我们可以用 XLOOKUP 实现以下查询:
示例 1:基础查询(根据姓名查工资)
1 | =XLOOKUP("王五", A2:A6, D2:D6) |
结果会返回 12000(王五的工资)
示例 2:向左查询(根据职位查姓名)
1 | =XLOOKUP("经理", C2:C6, A2:A6) |
结果会返回 王五(第一个匹配的经理姓名)
示例 3:带自定义未找到提示的查询
1 | =XLOOKUP("周八", A2:A6, B2:B6, "该员工不存在") |
结果会返回 该员工不存在(因为表格中没有 “周八”)
示例 4:反向查找(查找最后一个经理)
1 | =XLOOKUP("经理", C2:C6, A2:A6, , 0, -1) |
结果会返回 孙七(从后往前找的第一个经理)
示例 5:多条件查询(技术部的经理)
1 | =XLOOKUP(1, (B2:B6="技术部")*(C2:C6="经理"), A2:A6) |
结果会返回 王五(同时满足部门为技术部且职位为经理的人)
你可以将这些公式复制到 Excel 中,结合上述表格数据测试,更直观地感受 XLOOKUP 的灵活性。