Excel XLOOKUP函数：一个公式批量返回多列数据，告别VLOOKUP繁琐操作

如果你还在用 VLOOKUP 函数一个单元格一个单元格地匹配数据，那你的 Excel 效率可能还停留在上个时代。

当老板丢给你一张包含员工姓名、部门、工号、入职日期的总表，让你从中快速找出几十个特定员工的全部信息时，你会怎么做？是复制粘贴，还是写一串复杂的VLOOKUP、MATCH、INDEX组合公式，然后小心翼翼地向右拖动填充？这不仅容易出错，一旦数据源结构稍有变动，整个公式就可能“罢工”。

今天要讲的XLOOKUP，就是微软 Office 365 和 Excel 2021 及以上版本中，用来彻底解决这类“多条件、多结果”查找痛点的“终极武器”。它最核心的价值在于：用一个公式，就能批量返回多列查找结果，实现从“点对点”查找升级到“点对面”的数组化查找。

这篇文章不会只告诉你XLOOKUP的语法，而是会深入拆解它如何用“数组思维”重塑你的数据处理流程。你将学会如何用一个公式完成过去需要多个步骤才能实现的操作，并理解其背后的原理，从而真正掌握这个现代 Excel 数据分析的必备技能。

1. 这篇文章真正要解决的问题：告别繁琐的单列查找

在XLOOKUP出现之前，Excel 中的多列数据查找是一个典型的“体力活”。假设你需要根据“员工工号”，在总表中查找并返回该员工的“姓名”、“部门”和“薪资”。

传统做法（VLOOKUP 时代）：

1. 在“姓名”列写公式：=VLOOKUP(工号, 数据区域, 姓名所在列数, FALSE)
2. 在“部门”列写公式：=VLOOKUP(工号, 数据区域, 部门所在列数, FALSE)
3. 在“薪资”列写公式：=VLOOKUP(工号, 数据区域, 薪资所在列数, FALSE)

这种方法的弊端显而易见：

• 效率低下：每多返回一列，就需要多写一个公式。
• 维护困难：如果数据源中间插入或删除一列，所有VLOOKUP的“列索引号”都需要手动调整，否则就会返回错误数据。
• 无法向左查找：VLOOKUP只能查找返回查找列右侧的数据，如果“工号”在数据区域最右边，此方法直接失效。
• 公式冗长：当需要返回的列很多时，工作表会布满重复且脆弱的公式。

XLOOKUP要解决的，正是这种“一个条件对应多个结果”场景下的效率与健壮性问题。它通过引入“返回数组”的概念，让你只需一个公式，就能一次性吐出所有需要的结果，并且自带错误处理、近似匹配控制等高级功能。

2. XLOOKUP 基础概念与核心原理

在深入“多列查找”这个高级用法前，我们必须先理解XLOOKUP的基础，这能帮你理解它为何如此强大。

2.1 函数语法与参数解析

XLOOKUP的函数结构非常清晰：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

我们用一张表来快速理解每个参数：

2.2 核心原理：查找值与返回值的“数组化”联动

XLOOKUP强大的根源在于lookup_array和return_array这两个参数可以是任意大小和形状的区域，而不再像VLOOKUP那样被束缚在一个固定的“表格数组”里。

关键理解：

• lookup_array是“坐标轴”：你告诉 Excel 沿着哪条“线”（列或行）去寻找lookup_value。
• return_array是“结果集”：你告诉 Excel，当在“坐标轴”上找到目标后，从对应的“结果集”里把东西拿回来。这个“结果集”可以是一列、一行，甚至是一个多行多列的区域。

正是这种“坐标轴”与“结果集”的分离设计，为“多列返回”奠定了基础。当return_array是一个多列区域时，XLOOKUP就能一次性返回一个水平数组。

3. 环境准备与前置条件

在使用XLOOKUP进行多列查找之前，请务必确认你的 Excel 环境支持此函数。

• 操作系统：Windows 或 macOS 均可。
• Excel 版本：这是最关键的条件。XLOOKUP函数仅在以下版本中可用：
  • Microsoft 365 订阅版（持续更新）
  • Excel 2021 及更高版本（一次性购买）
  • Excel for the web (免费)
• 如何确认：在你的 Excel 中任意单元格输入=XLOOKUP(，如果出现函数提示，则说明可用。如果显示#NAME?错误，则说明你的版本不支持。

重要提醒：如果你需要将包含XLOOKUP公式的工作簿分享给他人，务必确认对方的 Excel 版本也支持此函数，否则他们打开时将看到#NAME?错误。对于需要广泛兼容的场景，可能需要准备INDEX+MATCH的备用方案。

4. 核心流程拆解：从单列查找到多列返回

让我们通过一个经典的员工信息查询案例，一步步拆解如何用XLOOKUP实现多列查找。假设我们有如下数据源表（Sheet1!A:D）：

现在，我们在另一个工作表（Sheet2）中，需要根据指定的工号，一次性查找出对应的姓名、部门和薪资。

4.1 第一步：构建查询条件与结果区域框架

在Sheet2中，我们这样布局：

• A1 单元格输入“查询工号：”
• B1 单元格输入具体的工号，例如E002。
• A3:A5 单元格分别输入“姓名”、“部门”、“薪资”，作为结果标签。
• B3:B5 单元格留空，准备放置我们的XLOOKUP公式。

4.2 第二步：编写“单点突破”的基础公式

我们先在B3单元格（对应“姓名”）写一个最基础的XLOOKUP公式，只返回一列数据，验证逻辑。

= XLOOKUP($B$1, Sheet1!$A$2:$A$5, Sheet1!$B$2:$B$5, "未找到")

• $B$1：绝对引用我们的查询条件“E002”。
• Sheet1!$A$2:$A$5：在数据源的“工号”列（查找数组）中搜索。
• Sheet1!$B$2:$B$5：找到后，从数据源的“姓名”列（返回数组）返回值。
• "未找到"：如果工号不存在，显示友好提示而非#N/A。

按下回车，B3单元格应该显示“李四”。恭喜，基础查找通了。

4.3 第三步：升级为“批量返回”的数组公式

这是最关键的一步。我们不需要在B4和B5分别写查找部门和薪资的公式。

我们只需要修改B3单元格的公式，将return_array参数从一个单列区域，扩展为一个多列区域。

将B3单元格的公式修改为：

= XLOOKUP($B$1, Sheet1!$A$2:$A$5, Sheet1!$B$2:$D$5, "未找到")

注意return_array的变化：从Sheet1!$B$2:$B$5（仅姓名列）变成了Sheet1!$B$2:$D$5（姓名、部门、薪资三列）。

神奇的事情发生了：当你按下回车后，B3单元格会显示“李四”，同时B4和B5单元格会自动被填充为“市场部”和“12000”！Excel 会显示一个蓝色的边框提示，这是一个“动态数组”公式，其结果“溢出”到了下方的单元格。

原理剖析：

1. XLOOKUP在A2:A5中找到了E002，位于第2行。
2. 由于return_array指定了B2:D5这个3列的区域，函数就返回了这个区域中第2行的所有值，即{"李四"， “市场部”， 12000}。
3. Excel 的动态数组功能自动将这个水平数组“垂直溢出”到B3:B5这三个单元格中。

至此，你已用一个公式完成了三列数据的查找。

5. 完整示例与代码实现：应对复杂场景

上面的例子是理想情况。实际工作中，数据源和查询需求往往更复杂。下面我们通过几个典型场景，展示XLOOKUP多列查找的完整实战代码。

5.1 场景一：返回不连续的多列数据

有时你需要返回的列在数据源中并不相邻。例如，根据工号查“姓名”和“薪资”，跳过中间的“部门”列。

数据源：同上。需求：在Sheet2的B3和B4分别返回姓名和薪资。

解决方案：使用CHOOSE函数构建一个虚拟的、连续的返回数组。 在Sheet2的B3单元格输入：

= XLOOKUP($B$1, Sheet1!$A$2:$A$5, CHOOSE({1,2}, Sheet1!$B$2:$B$5, Sheet1!$D$2:$D$5), "未找到")

• CHOOSE({1,2}, 列1, 列2)：这会创建一个虚拟的二维数组，第一列是“姓名”列的数据，第二列是“薪资”列的数据。
• XLOOKUP在这个虚拟数组中查找并返回对应行的两列数据，结果会溢出到B3和B4。

5.2 场景二：结合 FILTER 实现更灵活的多条件筛选

XLOOKUP擅长单条件多列返回。如果遇到多条件（如：部门=“技术部”且薪资>15000），则需要结合FILTER函数。

需求：找出技术部薪资高于15000的员工的所有信息。

在任意空白单元格输入数组公式（按 Ctrl+Shift+Enter 在旧版本中，Office 365直接回车）：

= FILTER(Sheet1!$A$2:$D$5, (Sheet1!$C$2:$C$5="技术部") * (Sheet1!$D$2:$D$5>15000), "无符合条件记录")

这个公式会返回一个满足两个条件的所有行（多行多列）的数据区域。你可以将其视为一个更强大的、先筛选再呈现的查找。

5.3 场景三：制作动态查询模板（配合数据验证）

将XLOOKUP与下拉菜单结合，可以制作一个非常友好的动态查询工具。

1. 创建下拉菜单：选中Sheet2的B1单元格，点击【数据】->【数据验证】->【序列】，来源选择Sheet1!$A$2:$A$5。这样B1就变成了一个工号下拉列表。
2. 写入动态公式：B3单元格的公式保持不变：

   =XLOOKUP($B$1, Sheet1!$A$2:$A$5, Sheet1!$B$2:$D$5, "请选择工号")

3. 使用：现在，你只需在B1单元格的下拉列表中选择不同工号，B3:B5区域的信息就会自动实时更新。

6. 运行结果与效果验证

如何验证你的XLOOKUP多列查找公式是否工作正常？

1. 直观验证：更改查询条件（如B1单元格的工号），观察下方返回的多列结果是否同步、准确地变化。
2. 错误测试：
   • 输入一个数据源中不存在的工号（如E999）。公式应返回你预设的[if_not_found]参数内容（如“未找到”或“请选择工号”），而不是#N/A。
   • 如果返回#VALUE!错误，通常是因为lookup_array和return_array的行数不一致。检查两个参数引用的区域是否具有相同的行数。
   • 如果返回#SPILL!错误，说明公式结果要“溢出”到的目标单元格区域中，有单元格非空。清空B3:B5等溢出区域即可。
3. 数组查看：选中公式单元格（B3），在编辑栏中高亮公式的return_array部分（例如Sheet1!$B$2:$D$5），然后按下F9键，可以临时计算出这部分引用的具体数值，帮助你理解函数正在处理的数据范围。

7. 常见问题与排查思路

即使理解了原理，在实际使用XLOOKUP进行多列查找时，你仍可能遇到一些问题。下表列出了最常见的问题及其解决方法。

8. 最佳实践与工程建议

要将XLOOKUP多列查找稳定、高效地应用于实际工作，请遵循以下最佳实践：

1. 使用结构化引用或定义名称：如果数据源是 Excel 表格（按 Ctrl+T 创建），可以使用表字段名，如=XLOOKUP([@工号], 表1[工号], 表1[[姓名]:[薪资]])。这样即使表格增减行，引用范围也会自动扩展，公式更易读、更健壮。
2. 始终锁定引用范围：在公式中，对lookup_array和return_array使用绝对引用（如$A$2:$A$100），防止复制公式时引用区域发生偏移。
3. 善用[if_not_found]参数：永远不要省略这个参数。将其设置为一个明确的提示信息，如“查无此人”、“数据缺失”，这比默认的#N/A错误更专业，也便于后续使用IFERROR进行统一处理。
4. 明确匹配模式：除非业务需要，否则始终使用精确匹配（match_mode为 0 或省略）。模糊匹配（1 或 -1）常用于数值区间查找，如根据分数查找等级。
5. 考虑逆向查找：XLOOKUP天生支持逆向查找（从右向左），无需像VLOOKUP那样绞尽脑汁。只需确保lookup_array和return_array的逻辑关系正确即可。
6. 性能优化：对于超大型数据集，如果数据已排序，可以尝试使用二进制搜索模式（search_mode为 2 或 -2），这能极大提升查找速度。
7. 文档化与协作：在复杂的查询模板中，使用批注说明公式的意图和关键参数。如果与使用旧版 Excel 的同事协作，务必提前沟通，或准备INDEX(MATCH())的兼容方案。
8. 组合其他函数，释放更大威力：XLOOKUP可以与SORT、FILTER、UNIQUE等动态数组函数无缝组合，构建出极其强大的数据查询与整理流水线，这是传统函数难以企及的。

XLOOKUP的多列查找能力，不仅仅是节省了几个公式那么简单。它代表了一种数据处理思维的转变：从针对单个单元格的“手工操作”，转向针对数据区域的“声明式编程”。你只需要告诉 Excel “我要什么”（根据A找B/C/D），而不是“一步一步怎么做”。

掌握这个功能后，你会发现自己处理报表、核对数据、搭建查询模板的效率有了质的飞跃。更重要的是，它构建的解决方案更加简洁、稳固，易于维护。下次再遇到多列查找的需求时，忘掉那些重复的VLOOKUP吧，尝试用这一个XLOOKUP公式去解决。开始可能会需要适应其数组思维，但一旦掌握，你就会彻底爱上这种高效与优雅。