你是不是也遇到过这样的场景在Excel里想根据一个条件查找并返回多列数据比如根据员工ID同时返回姓名、部门和工资。新手可能会用VLOOKUP一个个写老手可能会想到INDEXMATCH组合但无论哪种当需要返回的列数很多时公式就会变得又长又难维护。更头疼的是如果查找条件对应多个结果比如一个部门有多名员工传统的查找函数几乎束手无策。你可能会想“要是有一个函数能像数据库查询一样一次查找返回所有匹配的多列数据就好了。”其实Excel 365和WPS最新版里内置的XLOOKUP函数已经能部分解决这个问题但它也有局限原生XLOOKUP一次只能返回一个数组单列或单行。而今天要讲的是一个更“硬核”的玩法——用LAMBDA函数结合递归亲手“搓”出一个超级加强版的XLOOKUP。这个自制的递归XLOOKUP能实现两大传统函数难以企及的功能多值查找一个查找值对应多个结果时能全部返回。返回多列一次查找可以同时返回目标行中任意多列的数据。这不仅仅是炫技。在数据分析、报表自动化、数据清洗等实际工作中它能将原本需要多个辅助列或复杂数组公式才能完成的任务压缩成一个清晰的、可复用的自定义函数。本文将带你从零理解LAMBDA递归的思维并一步步手把手实现这个强大的查找工具。无论你用Excel 365还是WPS都能跟着操作。1. 为什么需要“手搓”XLOOKUP理解现有工具的边界在深入代码之前我们必须先搞清楚为什么放着好好的XLOOKUP不用要自己造轮子这源于实际工作中两个高频且棘手的痛点。痛点一一对多查找的天然短板假设你有一张销售记录表一个销售员有多条订单。你想找出“张三”的所有订单记录。使用XLOOKUP(“张三”, 销售员列, 订单详情区域)Excel只会返回找到的第一个匹配项。要获取所有结果你不得不借助FILTER函数如果版本支持或者更复杂的INDEXSMALLIF数组公式。后者不仅难以编写和调试而且计算效率在数据量大时会显著下降。痛点二返回多列时的公式冗余即使是一对一查找当需要返回目标行中的多列信息时例如根据工号查姓名、电话、邮箱标准的做法是写多个XLOOKUP公式XLOOKUP(工号, 工号列, 姓名列)XLOOKUP(工号, 工号列, 电话列)XLOOKUP(工号, 工号列, 邮箱列)这导致了大量的公式重复。虽然可以通过定义名称或使用CHOOSECOLS等新函数稍微简化但本质上仍是多个查找动作不够优雅也不利于后续维护。LAMBDA递归带来的范式转变LAMBDA函数允许我们创建自定义的、可复用的计算单元。而递归是LAMBDA函数最强大的特性之一它让一个函数能够调用自身。将两者结合我们就能设计一个逻辑函数先找到第一个匹配项并返回其多列数据然后“忘记”这个已找到的项在剩余的数据中继续调用自身寻找下一个匹配项如此循环直到找完所有匹配项。这样“手搓”出来的XLOOKUP其核心优势在于逻辑封装将复杂的多步查找逻辑打包成一个像MY_XLOOKUP(查找值, 查找区域, 返回区域)这样简单的函数。动态数组结果能自动溢出Spill完美适配Excel 365和WPS的动态数组环境。极致灵活你可以完全控制查找和返回的逻辑实现比原生函数更复杂的条件组合。接下来我们从最基础的LAMBDA和递归概念开始搭建。2. 核心概念十分钟搞懂LAMBDA与递归如果你对LAMBDA和递归感到陌生别担心。我们可以暂时忘掉那些计算机科学的复杂定义用Excel里最直观的方式来理解它们。LAMBDA是什么—— 给你命名的权力在Excel传统函数中我们是被动使用者。SUM(A1:A10)SUM是微软定义好的。LAMBDA函数则把“定义函数”的权力交给了你。它的基本语法是LAMBDA([参数1, 参数2, …], 计算公式)你可以把它想象成一个自定义的“配方”。例如创建一个计算面积的自定义函数LAMBDA(长, 宽, 长 * 宽)但这只是一个配方还没有名字。通常我们会通过“名称管理器”给它起个名字比如AREA。定义好后你就可以在工作表中像使用SUM一样使用AREA(5, 3)得到结果15。递归是什么—— 让函数“自我复制”递归简单说就是“自己调用自己”。听起来有点循环论证的味道但它需要一个关键条件来避免无限循环基线条件终止条件。想象一个倒计时从数字N开始每次减1直到0为止。 用伪代码表示这个递归逻辑就是倒计时(N): 如果 N 0, 停止。 否则显示 N然后调用 倒计时(N-1)。在Excel的LAMBDA中实现递归需要函数在计算公式的部分引用自己的名字。这打破了常规思维却是实现复杂迭代计算的钥匙。Excel/WPS中递归的实现机制在Excel 365和WPS支持LAMBDA的版本中实现递归的标准模式是定义一个调用自身的LAMBDA函数。由于函数在定义时自身还不完整通常需要借助一个“启动器”参数。最常见的方式是使用LAMBDA函数结合LET函数来创建可读性更高的递归逻辑。一个经典的例子是计算阶乘FactorialLET( Factorial, LAMBDA(n, IF(n1, 1, n * Factorial(n-1))), Factorial(5) )这个公式会计算出5的阶乘120。我们来拆解它LET函数用于定义局部变量。这里它定义了一个名为Factorial的变量。Factorial变量的值是一个LAMBDA函数LAMBDA(n, IF(n1, 1, n * Factorial(n-1)))。注意在LAMBDA的函数体里它调用了自己Factorial这就是递归。IF(n1, 1, ...)就是基线条件。当n小于等于1时返回1递归停止。最后LET的第三个参数Factorial(5)使用刚才定义的递归函数计算5的阶乘。理解了这个模式我们就掌握了建造“递归XLOOKUP”这座大厦最重要的砖块。3. 环境准备确认你的武器库在开始动手前请确保你的工具支持这些高级功能。软件版本要求Microsoft Excel: 需要 Microsoft 365 订阅版以前叫Office 365或 Excel 2021 及以后版本。这些版本支持动态数组函数如FILTER,SORT,UNIQUE和LAMBDA函数。你可以打开Excel在任意单元格输入LAMBDA(x, x)如果不报错则说明支持。WPS Office: 需要 WPS 2023 年秋季更新版本号如 12.1.0.xxx或更高版本。WPS在较新的版本中也实现了对LAMBDA和动态数组函数的支持。同样可以通过输入LAMBDA(x, x)来测试。关键功能开启通常这些功能默认是开启的。如果你的Excel版本较老或不支持将无法使用本文的所有公式。WPS用户请务必更新到最新版本以获得最佳兼容性。数据准备为了后续演示我们创建一个简单的示例数据源。你可以在一个新的工作表例如命名为“数据源”的A1:D10区域输入以下内容工号 (A)姓名 (B)部门 (C)销售额 (D)101张三销售部5000102李四技术部3000103王五销售部7000104赵六市场部4000105张三技术部6000106钱七销售部5500107孙八市场部4500108周九技术部3500109吴十销售部8000110郑十一市场部4800这个数据集的特点是存在重复的“姓名”张三并且我们可能需要根据“姓名”查找返回其“工号”、“部门”和“销售额”等多列信息。这正好是我们自制函数要解决的典型场景。4. 第一步构建基础递归查找框架单值单列万丈高楼平地起。我们先实现一个最简单的递归查找根据姓名返回所有匹配的工号单列。这能让我们专注于理解递归查找的核心流程。我们的目标是在另一个工作表如“查询页”的某个单元格如A2输入姓名“张三”在B2及向下的单元格中能动态列出所有工号为“张三”的工号。思路拆解函数设计我们需要一个自定义函数比如叫RECURSE_LOOKUP。它接收三个参数lookup_value查找值lookup_array在哪列找return_array返回哪列。递归逻辑基线条件如果在lookup_array里找不到lookup_value了就返回空数组{}。递归步骤 a. 用XMATCH或MATCH找到第一个匹配项的位置pos。 b. 取出return_array中对应位置的值first_result。 c. 从lookup_array和return_array中“移除”已找到的这一行这是递归的关键模拟“查找下一个”。 d. 将first_result和“在剩余数组中递归查找的结果”上下堆叠起来VSTACK。完整公式实现与解析在“查询页”的B2单元格输入以下单个、长长的公式LET( lookup_val, A2, // 假设A2单元格是我们要查找的姓名如“张三” data_lk, 数据源!$B$2:$B$11, // 查找列姓名列 data_rt, 数据源!$A$2:$A$11, // 返回列工号列 RecursiveLookup, LAMBDA(lk_val, lk_arr, rt_arr, LET( match_pos, XMATCH(lk_val, lk_arr, 0, 1), // 查找第一个匹配位置搜索模式为“下一个” IF(ISNA(match_pos), // 基线条件如果找不到返回空 {}, LET( first_result, INDEX(rt_arr, match_pos), // 取出第一个结果 // 核心递归从数组中移除已找到的行然后在剩余部分继续查找 remaining_lk, FILTER(lk_arr, (SEQUENCE(ROWS(lk_arr)) match_pos)), remaining_rt, FILTER(rt_arr, (SEQUENCE(ROWS(rt_arr)) match_pos)), next_results, RecursiveLookup(lk_val, remaining_lk, remaining_rt), // 递归调用 VSTACK(first_result, next_results) // 将本次结果与后续结果堆叠 ) ) ) ), // 执行递归查找 RecursiveLookup(lookup_val, data_lk, data_rt) )公式逐层解析LET定义局部变量让公式更清晰。定义了查找值lookup_val、查找数组data_lk、返回数组data_rt。定义递归函数RecursiveLookup这是核心。它接收三个参数查找值、查找数组、返回数组。在RecursiveLookup内部XMATCH(lk_val, lk_arr, 0, 1)查找第一个匹配项的位置。第4参数1表示“下一个”确保搜索方向正确。IF(ISNA(match_pos), {}, ...)基线条件。如果XMATCH返回错误#N/A说明找不到了返回空数组{}递归终止。如果找到了INDEX(rt_arr, match_pos)取出第一个匹配结果。FILTER(lk_arr, (SEQUENCE(ROWS(lk_arr)) match_pos))这是一个关键技巧。它创建了一个序列号1到总行数然后过滤掉行号等于match_pos的行从而生成一个“移除”了已找到行的新查找数组remaining_lk。对rt_arr进行同样操作得到remaining_rt。next_results, RecursiveLookup(lk_val, remaining_lk, remaining_rt)递归调用用同样的查找值但在“剩余数组”中继续查找。这是函数调用自己的地方。VSTACK(first_result, next_results)将本次找到的结果(first_result)和后续递归找到的所有结果(next_results)垂直堆叠起来形成最终的结果数组。最后一行RecursiveLookup(lookup_val, data_lk, data_rt)使用定义好的函数和初始参数开始执行递归计算。效果验证在A2单元格输入“张三”B2单元格的公式会自动溢出Spill在B2:B3显示101和105。这正是“张三”对应的两个工号。尝试将A2改为“李四”B2将只显示102。改为“钱七”则显示106。这一步成功意味着我们已经掌握了递归查找的灵魂“找到-移除-继续找”。接下来我们将在这个骨架上添加肌肉让它能返回多列。5. 核心升级实现多列返回与结果整理基础框架只能返回一列。在实际工作中我们往往需要同时返回多列信息。例如根据“张三”查找希望返回“工号”、“部门”、“销售额”三列。这需要对我们的递归函数进行升级。思路是每次递归找到匹配行时不是只取一列的值而是取一个“行切片”该行中我们需要的那几列的值然后将这些行切片堆叠成一个结果表。升级版公式多列返回假设我们在“查询页”的A2输入姓名希望在B2开始的区域返回该姓名对应的所有记录的“工号”、“部门”、“销售额”。我们在B2输入以下公式LET( lookup_val, A2, data_lk, 数据源!$B$2:$B$11, // 查找列姓名 // 返回多列工号、部门、销售额 data_rt, CHOOSECOLS(数据源!$A$2:$D$11, 1, 3, 4), // 选择第1,3,4列 RecursiveLookupMulti, LAMBDA(lk_val, lk_arr, rt_arr, LET( match_pos, XMATCH(lk_val, lk_arr, 0, 1), IF(ISNA(match_pos), {}, // 基线条件返回空数组 LET( // 关键变化取一行多列而不是一个单元格 first_row, INDEX(rt_arr, match_pos, SEQUENCE(, COLUMNS(rt_arr))), remaining_lk, FILTER(lk_arr, (SEQUENCE(ROWS(lk_arr)) match_pos)), // 注意remaining_rt 也需要过滤掉对应行但保持列结构 remaining_rt, FILTER(rt_arr, (SEQUENCE(ROWS(rt_arr)) match_pos)), next_rows, RecursiveLookupMulti(lk_val, remaining_lk, remaining_rt), // 使用 VSTACK 堆叠行 VSTACK(first_row, next_rows) ) ) ) ), RecursiveLookupMulti(lookup_val, data_lk, data_rt) )关键升级点解析data_rt定义变化CHOOSECOLS(数据源!$A$2:$D$11, 1, 3, 4)。这表示我们的返回区域不再是单列而是原始数据表A:D列中的第1、3、4列即工号、部门、销售额。CHOOSECOLS函数可以灵活地选择需要的列你也可以用INDEX实现。first_row获取方式变化INDEX(rt_arr, match_pos, SEQUENCE(, COLUMNS(rt_arr)))。这是公式的精华。INDEX(array, row_num, [column_num])当column_num省略时返回整行。但为了更精确和适应动态列数我们使用SEQUENCE(, COLUMNS(rt_arr))生成一个从1到总列数的水平序列例如{1,2,3}作为column_num参数。这告诉INDEX“返回第match_pos行以及所有列”。结果first_row就是一个包含多列值的单行数组如{101, “销售部”, 5000}。VSTACK堆叠行VSTACK(first_row, next_rows)。first_row是一个单行数组next_rows是后续递归返回的多行数组可能为空。VSTACK将它们垂直堆叠最终形成一个完整的结果表。效果验证与动态数组在A2输入“张三”B2单元格的公式会自动向右和向下溢出在B2:D3区域显示101 销售部 5000 105 技术部 6000这是一个完美的二维结果表它一次性完成了“一对多”查找并返回了“多列”数据。动态数组特性让结果自动填充无需手动拖动公式。处理返回列顺序与表头你可能希望结果有表头。这很简单在VSTACK函数中直接加入表头数组即可。修改RecursiveLookupMulti函数内部的VSTACK部分VSTACK({“工号”, “部门”, “销售额”}, first_row, next_rows)但更优雅的做法是在最外层的LET中定义表头然后与结果堆叠LET( lookup_val, A2, data_lk, 数据源!$B$2:$B$11, data_rt, CHOOSECOLS(数据源!$A$2:$D$11, 1, 3, 4), headers, {“工号”, “部门”, “销售额”}, // 定义表头 RecursiveLookupMulti, LAMBDA(lk_val, lk_arr, rt_arr, ... ), // 中间递归函数定义部分不变 final_result, RecursiveLookupMulti(lookup_val, data_lk, data_rt), IF(ROWS(final_result)0, headers, VSTACK(headers, final_result)) // 如果无结果只显示表头 )这样结果区域的第一行就是清晰的表头。6. 封装与复用创建真正的自定义函数将这么长的公式每次复制粘贴显然不现实。Excel和WPS的“名称管理器”允许我们将这个LAMBDA公式保存为一个像SUM一样可以直接调用的自定义函数。步骤一定义名称在Excel/WPS中按下Ctrl F3打开“名称管理器”。点击“新建”。在“名称”框中输入你想要的函数名例如MY_XLOOKUP_ALL。注意不要与现有函数冲突。在“引用位置”框中粘贴我们刚才构建的、去掉了最外层特定单元格引用和LET中初始变量赋值的LAMBDA公式核心部分。我们需要将其改造成一个通用的、带参数的函数。通用化公式模板一个设计良好的自定义查找函数应该接收清晰的参数。我们设计它接收四个参数lookup_value查找值。lookup_array查找值所在的单列区域。return_array需要返回的多列区域。[headers]可选结果表头数组。在“引用位置”粘贴以下公式LAMBDA(lookup_value, lookup_array, return_array, [headers], LET( RecursiveLookup, LAMBDA(lk_val, lk_arr, rt_arr, LET( match_pos, XMATCH(lk_val, lk_arr, 0, 1), IF(ISNA(match_pos), {}, LET( first_row, INDEX(rt_arr, match_pos, SEQUENCE(, COLUMNS(rt_arr))), remaining_lk, FILTER(lk_arr, (SEQUENCE(ROWS(lk_arr)) match_pos)), remaining_rt, FILTER(rt_arr, (SEQUENCE(ROWS(rt_arr)) match_pos)), next_rows, RecursiveLookup(lk_val, remaining_lk, remaining_rt), VSTACK(first_row, next_rows) ) ) ) ), raw_result, RecursiveLookup(lookup_value, lookup_array, return_array), IF(ISOMITTED(headers), // 判断是否提供了表头参数 raw_result, // 未提供直接返回结果 IF(ROWS(raw_result)0, headers, VSTACK(headers, raw_result)) // 提供则合并表头 ) ) )步骤二使用自定义函数定义成功后关闭名称管理器。现在在你的工作表中你可以像使用内置函数一样使用MY_XLOOKUP_ALL。 例如在“查询页”的任意单元格输入MY_XLOOKUP_ALL(A2, 数据源!$B$2:$B$11, CHOOSECOLS(数据源!$A$2:$D$11, 1,3,4), {“工号”, “部门”, “销售额”})这个公式清晰、简短并且可以在整个工作簿中任意调用。你成功地将复杂的递归逻辑封装成了一个强大的工具。7. 高级技巧与边界情况处理一个健壮的函数必须考虑各种边界情况和提供更多灵活性。以下是几个关键的增强点。技巧一处理未找到值的情况我们的基础版本在未找到值时返回空。这通常可以接受。但你可能希望返回一个友好的提示如“未找到”。修改基线条件部分的IF语句即可IF(ISNA(match_pos), IF(ISOMITTED(headers), {“未找到”}, headers), // 根据是否有表头返回不同提示 ... )技巧二使函数更“像”XLOOKUP——支持近似匹配和搜索模式原生XLOOKUP有第4参数未找到时返回的值、第5参数匹配模式、第6参数搜索模式。我们可以为我们的自定义函数添加类似参数增加其通用性。 以添加“匹配模式”为例修改LAMBDA定义和内部XMATCH调用LAMBDA(lookup_value, lookup_array, return_array, [headers], [match_mode], LET( match_type, IF(ISOMITTED(match_mode), 0, match_mode), // 默认为精确匹配 RecursiveLookup, LAMBDA(lk_val, lk_arr, rt_arr, LET( match_pos, XMATCH(lk_val, lk_arr, match_type, 1), // 使用传入的匹配模式 ... ) ), ... ) )使用时可以指定match_mode为0精确、-1小于、1大于等。技巧三性能优化——避免大规模数据的递归深度问题递归在Excel中如果层次过深通常超过几百层可能会遇到性能问题或计算限制。对于非常大的数据集纯递归可能不是最优解。替代方案是使用FILTER函数基础版FILTER(return_array, (lookup_array lookup_value))这个公式能直接实现一对多查找并返回多列且通常比递归更高效。那么我们为什么还要学递归学习价值递归是理解LAMBDA函数强大之处的绝佳案例是解决更复杂、FILTER无法直接处理的迭代问题的钥匙。逻辑控制递归允许你在迭代过程中嵌入更复杂的逻辑例如在找到每个结果后对其进行某种计算或判断。兼容性在极少数不支持FILTER但支持LAMBDA的环境未来某些场景递归方案是备选。因此实战建议是对于单纯的多值多列查找优先使用FILTER。当你需要实现“查找-处理-再查找”这类有状态的复杂迭代时递归才是你的王牌。8. 常见问题与排查指南在编写和使用这个自定义递归函数时你可能会遇到以下问题问题现象可能原因排查方式解决方案公式返回#NAME?错误1. 自定义函数名称未定义或拼写错误。2. 使用了当前版本不支持的新函数如XMATCH,VSTACK,CHOOSECOLS。1. 检查CtrlF3名称管理器中是否存在定义的名称。2. 尝试输入XMATCH(1,{1})若报错则不支持。1. 正确定义名称或更正拼写。2. 降级函数用MATCH代替XMATCH用IFERROR(INDEX(...), “”)构造数组代替VSTACK会变复杂。公式返回#CALC!错误递归可能进入了无限循环或超出了迭代限制。检查基线条件是否一定能被触发。例如查找值在数组中永远存在且FILTER移除行的逻辑有误。1. 确保基线条件ISNA(match_pos)有效。2. 在FILTER函数中确保SEQUENCE(ROWS(arr)) match_pos逻辑正确能真正移除已处理行。3. 用一个小数据集如3行测试逐步调试。结果只返回第一个匹配项递归逻辑未能正确执行。通常是remaining_lk/rt计算有误导致递归调用始终在原始数组上查找。在公式中临时使用F9键分别计算match_pos、remaining_lk的值看remaining_lk是否比原数组少了一行。重点检查生成remaining_lk和remaining_rt的FILTER公式。确保SEQUENCE(ROWS(lk_arr))生成的是垂直数组且 match_pos比较能产生正确的布尔数组。返回多列时列顺序或内容不对CHOOSECOLS或INDEX取列的参数设置错误。核对CHOOSECOLS的列索引参数或INDEX中SEQUENCE生成的列号序列。使用CHOOSECOLS(数据源!$A$2:$D$11, 1,3,4)这样的形式明确指定列。用F9查看INDEX(rt_arr, match_pos, SEQUENCE(, COLUMNS(rt_arr)))的结果是否正确。WPS中公式部分函数不支持WPS版本较旧未完全实现所有新函数。查看WPS官方文档或更新日志确认函数支持情况。更新WPS到最新版本。如果仍不支持XMATCH或VSTACK需要寻找替代组合这会大幅增加公式复杂度建议优先升级。公式计算缓慢数据量非常大数万行递归计算开销大。观察计算状态栏。对于大数据集递归不是最佳选择。如前所述考虑使用FILTER(return_array, (lookup_array lookup_value))作为替代方案。它针对数组操作进行了优化效率更高。9. 最佳实践与工程化建议将这样一个强大的自定义函数投入实际工作需要一些工程化的考量。1. 命名规范与文档化函数名取一个见名知意的名字如LOOKUP_ALL_MATCHES、MULTI_FETCH。避免使用FUNC1这类无意义名称。参数注释虽然在名称管理器中无法直接注释但可以在工作簿中创建一个“使用说明”工作表详细记录每个参数的用途、示例和注意事项。示例区域在“使用说明”工作表中留出一个区域用实际数据展示函数的各种用法和效果。2. 数据源结构化与引用使用表格Table强烈建议将数据源转换为Excel表格CtrlT。这样你的数据区域引用可以从数据源!$A$2:$D$11变为结构化引用如Table1[#All]或Table1[[工号]:[销售额]]。当数据增减时引用范围会自动扩展无需手动修改公式。定义名称可以为查找列和返回列区域定义名称如Lookup_Name、Return_Data。这样自定义函数公式会更简洁MY_XLOOKUP_ALL(A2, Lookup_Name, Return_Data)。3. 错误处理与稳健性输入验证可以在自定义函数内部最外层增加对参数类型的简单判断。例如确保lookup_array和return_array行数一致。IF(ROWS(lookup_array) ROWS(return_array), “错误查找列与返回列行数不一致”, … )空值处理考虑查找值为空或查找数组为空的情况在函数开头进行处理返回空或提示。4. 性能与维护区分场景明确你的自定义函数是用于解决FILTER等原生函数难以处理的复杂迭代逻辑。对于简单的多条件筛选直接使用FILTER、SORT、UNIQUE等组合往往更高效、更易维护。版本备份复杂的LAMBDA公式一旦定义修改起来不如普通公式直观。建议在定义前将公式文本保存在文本文件或单元格注释中。团队共享自定义函数保存在工作簿内。将该工作簿保存为模板.xltx或加载宏.xlam可以方便地在团队内共享使用。通过这次“手搓”XLOOKUP的旅程你收获的不仅仅是一个强大的查找工具。更重要的是你掌握了利用LAMBDA和递归将复杂逻辑封装成简单工具的思维方式。这种能力让你在面对Excel中那些看似无解的问题时多了一种自己创造解决方案的可能。下次当同事为复杂的多层查找而头疼时你可以淡定地说“试试我这个自定义函数。”