如何在新增工作表时自动更新公式（将新工作表纳入公式）

针对你需要公式自动包含新增工作表、无需手动扩展的需求，以下是几种实用方法：

方法1：基于工作表列表的动态数组公式（Excel 365/2021 推荐） #

这个方法利用动态数组功能，自动遍历指定工作表，无需手动嵌套IFERROR：

1. 准备工作表名称列表
   创建一个名为SheetList的工作表，在A列按查询优先级输入所有要检索的工作表名称（比如先填Test、New，后续新增工作表直接追加到A列即可，预留10个位置足够）。如果不同工作表的返回列数不同，在B列对应填写每个工作表的返回列序号（比如Test填5，New填3）。

2. 使用动态数组公式
   在目标单元格输入以下公式：

   =IFERROR(
     XLOOKUP(
       E206,
       TOCOL(INDIRECT("'"&SheetList!$A$1:$A$12&"'!$C:$C")),
       TOCOL(INDEX(INDIRECT("'"&SheetList!$A$1:$A$12&"'!$C:$P"),,SheetList!$B$1:$B$12)),
       "-"
     ),
     "-"
   )
   

   公式说明：

   • INDIRECT根据工作表名称动态引用对应列
   • TOCOL将多工作表的查询列/返回列合并为单一数组
   • XLOOKUP在合并数组中查找匹配值，返回对应结果
   • 后续新增工作表时，只需在SheetList的A、B列添加对应信息，公式自动生效

方法2：自定义名称 + EVALUATE（兼容旧版Excel） #

如果使用旧版Excel，可通过自定义名称生成动态嵌套的IFERROR公式：

1. 同方法1创建SheetList工作表，A列存工作表名称，B列存返回列序号。

2. 定义自定义名称
   打开「公式」→「定义名称」，设置：

   • 名称：DynamicLookup
   • 引用位置：

     =EVALUATE(TEXTJOIN(",IFERROR(VLOOKUP(E206,'"&SheetList!$A$1:$A$12&"'!$C$2:$P$48,"&SheetList!$B$1:$B$12&",FALSE),",TRUE)&"""---""")
     

3. 调用自定义名称
   在目标单元格输入：

   =DynamicLookup
   

   新增工作表时，更新SheetList即可自动扩展公式逻辑。

方法3：简化版（所有新增工作表结构一致） #

如果后续新增的工作表都和New表结构相同（C列查找，返回第3列），可以直接用TEXTJOIN生成嵌套公式：

=IFERROR(VLOOKUP(E206,Test!$C$2:$P$48,5,FALSE),IFERROR(TEXTJOIN("",TRUE,IFERROR(VLOOKUP(E206,INDIRECT("'"&SheetList!$A$2:$A$12&"'!$C$2:$E$48"),3,FALSE),""))),"-")

注：SheetList!$A$2:$A$12包含New和后续新增的工作表名称，A1留空或填Test单独处理。

内容的提问来源于stack exchange，提问作者Askingfordummies