小技巧之Excel实用函数——XLOOKUP与VLOOKUP
- 2026-09-08 06:52:32
在Excel数据处理场景中，查找匹配是高频需求，而VLOOKUP函数曾是行业标配，XLOOKUP作为其升级替代函数，解决了传统函数的诸多局限。本节课将从函数定义、语法结构、实操案例、优劣对比四个维度，手把手教你掌握这两个最实用的查找函数，覆盖日常办公80%以上的查找场景。
一、基础必备：VLOOKUP函数
VLOOKUP（Vertical Lookup，垂直查找）是基于列的查找函数，核心功能是“根据指定条件，在数据区域的第一列匹配，返回同一行中其他列的内容”，适用于数据结构固定、查找方向单一的场景。
1. 语法结构
完整语法：VLOOKUP(查找值, 查找区域, 返回列序号, [匹配模式])
查找值：需要匹配的核心条件（如姓名、编号），可直接输入值、单元格引用或表达式。
查找区域：包含查找值和返回内容的数据范围，查找值必须在该区域的第一列（这是VLOOKUP的核心限制）。
返回列序号：在查找区域中，需要返回内容所在的列数（从查找列开始计数，最小为1）。
匹配模式：可选参数，分为两种： FALSE（或0）：精确匹配，找不到时返回#N/A错误，适合查找唯一值（如身份证号、编号），日常办公首选。
TRUE（或1/省略）：近似匹配，需先对查找列排序，找不到精确值时返回小于查找值的最大数据，适合区间查找（如成绩等级、个税税率）。
2. 实操案例（精确匹配）
场景：根据员工编号，查找对应员工的部门和薪资（数据区域：A2:C10，A列为编号、B列为姓名、C列为部门、D列为薪资）。
// 查找编号为1003的员工部门（返回C列，序号3）
=VLOOKUP(1003, A2:D10, 3, FALSE)
// 查找编号为1005的员工薪资（返回D列，序号4），引用单元格A12作为查找值
=VLOOKUP(A12, A2:D10, 4, 0)
结果说明：若A列存在1003，将返回对应行C列的部门名称；若不存在，返回#N/A错误，可搭配IFERROR函数处理错误值（如=IFERROR(VLOOKUP(...), "无此员工")）。
3. VLOOKUP的核心局限
1. 查找方向受限：仅能从左向右查找，若返回列在查找列左侧，无法直接使用；
2. 列序号易出错：若查找区域中间插入/删除列，返回列序号需手动修改，否则结果失真；
3. 匹配灵活性低：仅支持垂直查找，且无法默认返回最后一个匹配值，需额外嵌套函数。
二、升级首选：XLOOKUP函数
XLOOKUP是Excel 365/2021及以上版本新增的查找函数，被誉为“VLOOKUP的终极替代者”，支持双向查找、动态列引用、默认值设置等功能，彻底解决了传统查找函数的痛点。
1. 语法结构（简化版，满足日常需求）
常用语法：XLOOKUP(查找值, 查找列, 返回列, [未找到时的默认值], [匹配模式], [搜索模式])
查找值：与VLOOKUP一致，为匹配条件。
查找列：单独指定用于匹配的列（无需包含返回列），灵活度更高。
返回列：单独指定需要返回内容的列，可在查找列左侧或右侧，无方向限制。
未找到时的默认值：可选参数，找不到匹配值时返回自定义内容（如“无结果”），无需额外嵌套IFERROR。
匹配模式：可选，0=精确匹配（默认）、-1=近似匹配（小于查找值的最大项）、1=近似匹配（大于查找值的最小项）、2=通配符匹配（支持*、?）。
搜索模式：可选，1=从顶部开始搜索（默认）、-1=从底部开始搜索（返回最后一个匹配值），无需嵌套其他函数。
2. 实操案例（覆盖核心场景）
沿用上述员工数据（A列编号、B列姓名、C列部门、D列薪资），演示XLOOKUP的优势用法：
// 场景1：精确查找，自定义默认值（查找1006号员工部门，无结果返回“无此员工”）
=XLOOKUP(1006, A2:A10, C2:C10, "无此员工")
// 场景2：反向查找（根据姓名查找编号，返回列在查找列左侧，VLOOKUP无法直接实现）
=XLOOKUP("张三", B2:B10, A2:A10)
// 场景3：返回最后一个匹配值（若有重复编号，返回最后一行对应的薪资）
=XLOOKUP(1002, A2:A10, D2:D10, "无结果", 0, -1)
// 场景4：通配符匹配（查找姓“李”的员工部门，*表示任意字符）
=XLOOKUP("李*", B2:B10, C2:C10)
3. XLOOKUP的核心优势
双向查找自由：返回列可在查找列左侧或右侧，无需调整数据结构。
动态列引用：插入/删除列时，只要查找列和返回列的引用范围不变，结果不受影响。
简化错误处理：内置默认值参数，替代IFERROR嵌套，公式更简洁。
灵活搜索模式：支持从底部搜索、通配符匹配，覆盖更多复杂场景。
三、VLOOKUP与XLOOKUP对比及适用场景
对比维度 | VLOOKUP | XLOOKUP |
|---|---|---|
Excel版本要求 | 全版本支持（兼容旧版） | 仅Excel 365/2021及以上版本 |
查找方向 | 仅从左向右 | 双向自由查找 |
错误处理 | 需嵌套IFERROR | 内置默认值参数 |
重复值处理 | 仅返回第一个匹配值 | 支持返回最后一个匹配值 |
适用场景 | 旧版Excel、简单左→右精确查找 | 新版Excel、复杂查找（反向、通配符、重复值） |
四、实战技巧与注意事项
1. 批量返回多列数据
XLOOKUP支持批量返回多列，只需将返回列设为连续区域即可，例如：=XLOOKUP(1003, A2:A10, C2:D10)，将同时返回部门（C列）和薪资（D列）。
2. 绝对引用的重要性
复制公式到其他单元格时，需对查找列/区域添加绝对引用（加$符号），避免引用范围偏移。例如：VLOOKUP(A12, $A$2:$D$10, 4, 0)、XLOOKUP(A12, $A$2:$A$10, $D$2:$D$10)。
