Excel LAMBDA递归实现多值查找与动态数组返回

你有没有遇到过这样的场景：手里有一份员工信息表，需要根据工号查找对应的姓名、部门、岗位，甚至更多信息。新手可能会用VLOOKUP一个个查，老手可能会想到用XLOOKUP或者INDEX+MATCH组合。但如果你要查找的工号可能对应多条记录（比如一个人有多个历史岗位），或者你需要一次性返回这个工号对应的所有列信息，常规的查找函数就会显得力不从心。要么你得写一串公式，要么你得借助复杂的数组公式，维护起来非常头疼。

这正是XLOOKUP函数设计时的一个经典痛点：它默认只返回第一个匹配项。当你的需求升级为“多值查找”或“返回多列”时，很多人会开始寻找插件、写VBA，或者陷入复制粘贴的循环。但今天，我想带你用Excel里一个被严重低估的功能——LAMBDA递归，来“手搓”一个属于你自己的、更灵活的超级查找工具。这不仅仅是学会一个公式，更是理解如何用函数式编程的思维，把Excel从一个计算工具，升级为一个可定制、可扩展的自动化平台。无论是WPS还是Excel，只要支持LAMBDA，你就能实现。

1. 为什么我们需要“手搓”XLOOKUP：理解单次查找与批量处理的鸿沟

在深入代码之前，我们必须先想清楚一个问题：Excel内置的XLOOKUP已经很强大了，为什么还要自己造轮子？

答案不在于XLOOKUP不好，而在于它和大多数内置函数一样，是为“单点查询”设计的。它的工作模式是：我给你一个查找值，在一个查找区域里找到第一个匹配项，然后从对应的返回区域里拿一个结果回来。这个模式在80%的场景下高效且完美。

但当场景变得复杂时，这个模式的局限性就暴露了：

1. 多值查找（一对多）：查找值在数据源里重复出现，你需要所有匹配的记录，而不是第一条。例如，按部门查找所有员工，按产品型号查找所有销售记录。
2. 返回多列：找到匹配项后，你需要同时获取该行多个列的信息。虽然可以用多个XLOOKUP或者INDEX配合MATCH实现，但公式会冗长，且每次增加一列都要修改公式。
3. 动态结果数组：你希望结果能自动展开或收缩，随着查找结果的数量动态变化，而不是固定在某几个单元格里。

这些需求指向了一个共同的解决方案：我们需要一个能“批量处理”并“动态返回数组”的查找机制。而实现这一机制的核心，就是LAMBDA函数的递归能力。递归，简单说就是函数自己调用自己，通过不断缩小问题规模来解决问题。在Excel里，我们可以用递归来遍历数据，收集所有符合条件的记录。

所以，“手搓XLOOKUP”的真正目标，不是复制一个XLOOKUP，而是构建一个能处理更复杂查找逻辑的、可定制的查找引擎。

2. 构建基石：理解LAMBDA递归的核心模式与安全边界

在动手写一个具体的多值查找函数之前，我们必须先搭建好递归的“脚手架”。没有这个基础，复杂的逻辑很容易变成一团乱麻。

递归在ExcelLAMBDA中的实现，依赖于两个关键点：

1. 一个自定义的LAMBDA函数，它至少接受一个参数（通常是剩余待处理的数据）。
2. 一个终止条件：当满足某个条件时（比如数据已处理完），函数不再调用自身，而是返回一个最终结果（比如空数组{}）。
3. 一个递归调用：在终止条件不满足时，函数处理当前数据的第一条，然后调用自身处理剩余的数据。

让我们先看一个最简单的递归例子，理解这个模式：

=LET( data, A2:A10, // 假设这是我们的数据区域 // 定义一个递归函数Reverse，用于反转数组 Reverse, LAMBDA(arr, IF(ROWS(arr)=0, // 终止条件：如果数组为空 {}, // 返回空数组 LET( first, TAKE(arr, 1), // 取第一个元素 rest, DROP(arr, 1), // 取剩余元素 VSTACK(Reverse(rest), first) // 递归：反转剩余部分，再堆叠第一个元素 ) ) ), Reverse(data) // 调用函数 )

这个Reverse函数清晰地展示了递归三要素：

• 终止条件：IF(ROWS(arr)=0, {}, ...)。没有数据了，就返回空。
• 处理当前：LET(first, TAKE(arr, 1), ...)。取出当前数组的第一个元素。
• 递归调用：VSTACK(Reverse(rest), first)。对剩余部分(rest)调用Reverse自身，然后将结果与当前处理的元素(first)组合。

在开始更复杂的查找递归前，我们必须设立安全边界：

• 深度限制：Excel的递归默认有迭代次数限制。虽然对于通常的数据量（几百几千行）足够，但理论上递归深度过大会导致计算错误。我们的递归逻辑应确保每次调用都能有效减少问题规模。
• 明确输入输出：递归函数内部要清晰定义每一步的输入（当前状态）和输出（处理后的结果）。混乱的状态管理是递归调试的噩梦。
• 先验证后扩展：永远先用极小的样本数据（比如5行）验证递归逻辑是否正确，再应用到全量数据。在递归函数中插入TAKE函数临时限制处理行数，是很好的调试习惯。

掌握了这个模式，我们就可以把它应用到查找问题上。

3. 实战一：用递归实现“一对多”查找（返回单列）

假设我们有一个销售记录表，A列是“销售员”，B列是“销售额”。现在需要根据指定的销售员姓名，找出他所有的销售额记录。

传统的XLOOKUP只能返回第一个。我们的递归思路是：

1. 遍历“销售员”列。
2. 如果当前行的销售员等于查找值，则将对应的“销售额”收集起来。
3. 继续遍历下一行，直到所有行处理完毕。

下面是具体的LAMBDA递归公式构建过程。我们将其定义为一个名为MULTI_LOOKUP_SINGLE的自定义函数（通过LET定义，或未来可存入名称管理器）：

=LET( lookup_value, "张三", // 要查找的销售员 lookup_array, A2:A100, // 销售员列 return_array, B2:B100, // 销售额列 // 核心递归函数 MultiLookupCore, LAMBDA(l_array, r_array, result_so_far, IF(ROWS(l_array)=0, // 终止条件：查找数组为空 result_so_far, // 返回目前已收集的结果 LET( current_lookup, TAKE(l_array, 1), // 当前行查找值 current_return, TAKE(r_array, 1), // 当前行返回值 new_result, IF(current_lookup = lookup_value, VSTACK(result_so_far, current_return), // 匹配则堆叠结果 result_so_far // 不匹配则保持原结果 ), // 递归调用：处理剩余行 MultiLookupCore(DROP(l_array, 1), DROP(r_array, 1), new_result) ) ) ), // 初始化并调用递归函数，初始结果为#N/A（可用空值{}代替） final_result, MultiLookupCore(lookup_array, return_array, #N/A), // 清理初始的#N/A标记（如果结果是数组，FILTER是更优选择） FILTER(final_result, final_result <> #N/A) )

公式拆解与关键点：

• MultiLookupCore是递归函数，它接受三个参数：剩余待查找的数组(l_array)、剩余待返回的数组(r_array)、以及截至目前已收集的结果(result_so_far)。
• 终止条件：当l_array没有行时，意味着所有数据已遍历完毕，返回result_so_far。
• 处理当前行：用TAKE(..., 1)获取当前第一行的查找值和返回值。
• 条件收集：判断current_lookup是否等于目标值。如果相等，使用VSTACK将current_return堆叠到已有结果上；否则，结果保持不变。
• 递归推进：使用DROP(..., 1)移除已处理的第一行，生成新的“剩余数组”，并调用MultiLookupCore自身。
• 初始化与清理：初始调用时，已收集结果设为#N/A。递归结束后，用FILTER过滤掉这个初始标记，得到纯净的匹配结果数组。

这个公式的结果是一个垂直数组，动态包含了“张三”的所有销售额。如果“张三”有3条记录，结果就是3行；如果没有，结果就是空。

注意：在实际使用中，更优雅的做法是将#N/A初始值替换为空数组{}，并在递归函数中直接处理空数组的堆叠。上述示例使用#N/A是为了更清晰地展示“收集-过滤”的过程逻辑。优化后的版本应直接操作数组。

4. 实战二：进阶递归，实现“返回多列”的查找

“一对多”查找返回单列已经很有用，但更强大的场景是：根据一个工号，一次性返回该员工的所有信息（姓名、部门、岗位、邮箱等）。这需要我们的递归函数能处理并返回多个列。

思路需要升级：我们不再只是收集一个值，而是要收集一行数据（一个数组片段）。同时，我们需要动态决定返回哪几列。

假设数据表从A列到E列分别是：工号、姓名、部门、岗位、邮箱。我们要根据工号查找，并返回后四列信息。

=LET( lookup_value, "EMP001", lookup_col, A2:A100, // 工号列 data_table, B2:E100, // 需要返回的数据区域（多列） return_col_index, {1,2,3,4}, // 一个数组，指定返回data_table中的第1,2,3,4列（即全部） MultiLookupMultiCol, LAMBDA(l_col, d_table, r_idx, result_so_far, IF(ROWS(l_col)=0, result_so_far, LET( current_key, TAKE(l_col, 1), current_row, TAKE(d_table, 1), // 判断是否匹配 matched, current_key = lookup_value, // 如果匹配，则提取当前行中指定的列 extracted_row, IF(matched, CHOOSECOLS(current_row, r_idx), {} // 不匹配则返回空行，后续需要处理空行堆叠 ), new_result, VSTACK(result_so_far, extracted_row), // 递归：移动到下一行 MultiLookupMultiCol(DROP(l_col, 1), DROP(d_table, 1), r_idx, new_result) ) ) ), // 初始化调用，初始结果设为空数组{} raw_result, MultiLookupMultiCol(lookup_col, data_table, return_col_index, {}), // 移除可能因空行堆叠产生的完全空行（可选，取决于CHOOSECOLS对不匹配行的处理） FILTER(raw_result, BYROW(raw_result, LAMBDA(r, SUM(--(r<>""))>0))) )

核心升级点解析：

1. 参数化返回列：通过return_col_index参数（如{1,2,3,4}或{2,4}），我们可以灵活指定返回数据表中的哪些列，无需修改函数内部逻辑。
2. CHOOSECOLS函数：这是Excel 365和WPS新版中的关键函数，它根据索引数组从一行中选取指定的列，完美适配动态列返回需求。
3. 行级操作：递归的核心单元从“值”变成了“行”（current_row）。匹配时，我们对整行进行列选择操作。
4. 空行处理：当不匹配时，我们返回空数组{}。在递归的VSTACK过程中，需要妥善处理空数组与已有数组的堆叠，或者像示例最后一样，用FILTER和BYROW组合过滤掉全空的行。

这个公式最终会返回一个动态数组，行数等于匹配到的记录数，列数等于return_col_index中指定的数量。它实现了真正意义上的“动态多列返回”。

5. 从公式到工具：封装、优化与工程化建议

写出了一个能工作的递归公式，只是第一步。要让它在日常工作中真正可靠、易用，我们需要进行工程化处理。

5.1 封装为可重用的自定义函数

每次都写这么长的LET公式不现实。我们可以利用Excel的“名称管理器”或WPS的“定义名称”功能，将核心递归逻辑封装起来。

1. 在Excel/WPS中，按下Ctrl+F3打开名称管理器。
2. 新建一个名称，例如叫做MY_XLOOKUP_ALL。
3. 在“引用位置”中，粘贴我们优化后的、泛化的LAMBDA公式。这个公式需要接受参数。

一个封装好的示例（概念性代码，需根据实际需求调整）：

=LAMBDA(lookup_value, lookup_array, return_array, [return_cols], LET( cols, IF(ISOMITTED(return_cols), SEQUENCE(1, COLUMNS(return_array)), return_cols), core, LAMBDA(l_arr, r_arr, result, IF(ROWS(l_arr)=0, result, LET( cur_l, TAKE(l_arr,1), cur_r, TAKE(r_arr,1), match, cur_l = lookup_value, extract, IF(match, CHOOSECOLS(cur_r, cols), {}), new_res, VSTACK(result, extract), core(DROP(l_arr,1), DROP(r_arr,1), new_res) ) ) ), raw, core(lookup_array, return_array, {}), FILTER(raw, BYROW(raw, LAMBDA(r, SUM(--(r<>""))>0))) ) )

4. 保存后，在工作表中就可以像普通函数一样使用：=MY_XLOOKUP_ALL("张三", A2:A100, B2:E100, {1,3,4})。

5.2 性能与稳定性优化

递归公式在处理大量数据时可能变慢。以下优化策略至关重要：

• 限制递归范围：不要引用整列（如A:A），应使用精确的范围（如A2:A1000）或动态数组（如XLOOKUP返回的范围）。TAKE/DROP在全列引用上效率很低。
• 使用LET缓存中间变量：正如我们在所有示例中做的，LET可以避免重复计算相同的子表达式，提升效率。
• 考虑非递归替代方案：对于“一对多”查找，FILTER函数通常是更简单高效的选择（=FILTER(return_array, lookup_array=lookup_value)）。我们“手搓”的价值在于理解原理和应对FILTER无法直接处理的更复杂嵌套逻辑。在实际工作中，应优先使用内置的FILTER。
• 错误处理：在自定义函数中加入IFERROR逻辑，处理查找值为空、数组大小不一致等情况，返回友好的提示信息或空值。

5.3 清晰定义适用边界

这个“手搓”的递归查找工具强大，但并非银弹。请明确它的最佳使用场景：

非常适合：

• 学习LAMBDA递归思想的绝佳案例。
• 在FILTER函数不可用或受限的环境下（如某些旧版本），实现类似功能。
• 处理需要复杂中间逻辑的查找过程（例如，查找过程中需要动态计算或判断）。
• 作为构建更复杂自定义函数的基础模块。

效率可能不足：

• 超大规模数据（数万行）的简单查找，内置的FILTER或数据透视表性能更好。
• 仅需要返回第一个匹配值的场景，直接用XLOOKUP。
• 对公式维护性要求极高、团队协作的场景，复杂的递归公式可能增加理解成本。

通过这次“手搓XLOOKUP”的旅程，我们完成的不仅仅是一个多值查找工具。我们实际上实践了一套在Excel中解决复杂问题的函数式编程方法论：将问题分解，用递归遍历数据，用高阶函数组合结果，最后封装成可复用的组件。这种能力，让你在面对那些看似需要VBA或插件才能解决的Excel难题时，多了一种强大而优雅的纯函数解决方案。下次当你的查找需求超越XLOOKUP的边界时，不妨想想：是不是可以用递归，让数据自己“走”出来。