跨3个工作簿的Index & Match函数应用：用一个引用另外两个库存表

需求说明 #

需要查询唯一序列号的库存信息，该序列号仅存在于SiteA_INV.xlsx或SiteB_INV.xlsx其中一个工作簿的Current工作表中，原公式仅支持单工作簿查询，需扩展为双工作簿查询。

原公式逻辑回顾 #

原公式通过INDEX+SUMPRODUCT+MATCH组合实现单工作簿查询：

• SUMPRODUCT定位匹配序列号（$K$3）的行号
• MATCH定位目标列（$J$6对应的列标题）
• INDEX返回对应单元格值
• IFERROR处理无匹配时的提示

=IFERROR((@INDEX([SiteA_INV.xlsx]Current!$A$2:$I$1910,SUMPRODUCT(([SiteA_INV.xlsx]Current!$A$2:$I$1910=$K$3)*(ROW([SiteA_INV.xlsx]Current!$A$2:$I$1910)))-@ROW([SiteA_INV.xlsx]Current!$A$1:$I$1),MATCH($J$6,[SiteA_INV.xlsx]Current!$A$1:$I$1,0))),"Enter Search Criteria")

修改方案 #

方案1：嵌套IFERROR扩展原公式 #

直接在原公式的IFERROR中嵌套第二个工作簿的查询逻辑，先查SiteA，无匹配则自动查SiteB：

=IFERROR(
    @INDEX([SiteA_INV.xlsx]Current!$A$2:$I$1910,SUMPRODUCT(([SiteA_INV.xlsx]Current!$A$2:$I$1910=$K$3)*(ROW([SiteA_INV.xlsx]Current!$A$2:$I$1910)))-@ROW([SiteA_INV.xlsx]Current!$A$1:$I$1),MATCH($J$6,[SiteA_INV.xlsx]Current!$A$1:$I$1,0)),
    IFERROR(
        @INDEX([SiteB_INV.xlsx]Current!$A$2:$I$1910,SUMPRODUCT(([SiteB_INV.xlsx]Current!$A$2:$I$1910=$K$3)*(ROW([SiteB_INV.xlsx]Current!$A$2:$I$1910)))-@ROW([SiteB_INV.xlsx]Current!$A$1:$I$1),MATCH($J$6,[SiteB_INV.xlsx]Current!$A$1:$I$1,0)),
        "Serial Not Found"
    )
)

• 注意：两个工作簿的Current工作表结构需完全一致（列标题、数据区域范围匹配）
• 最终无匹配时的提示可按需修改（比如替换"Serial Not Found"）

方案2：使用XLOOKUP简化公式（Excel 365/2021及以上版本） #

如果使用的是支持XLOOKUP的Excel版本，可大幅简化公式，利用IFNA实现双区域查询：

=IFNA(
    XLOOKUP($K$3,[SiteA_INV.xlsx]Current!$A:$A,[SiteA_INV.xlsx]Current!$A:$I,,0),
    IFNA(
        XLOOKUP($K$3,[SiteB_INV.xlsx]Current!$A:$A,[SiteB_INV.xlsx]Current!$A:$I,,0),
        "Serial Not Found"
    )
)

• 若仅需返回指定列（对应$J$6的列），可调整XLOOKUP的返回区域为目标列：

  =IFNA(
      XLOOKUP($K$3,[SiteA_INV.xlsx]Current!$A:$A,XLOOKUP($J$6,[SiteA_INV.xlsx]Current!$A$1:$I$1,[SiteA_INV.xlsx]Current!$A:$I),,0),
      IFNA(
          XLOOKUP($K$3,[SiteB_INV.xlsx]Current!$A:$A,XLOOKUP($J$6,[SiteB_INV.xlsx]Current!$A$1:$I$1,[SiteB_INV.xlsx]Current!$A:$I),,0),
          "Serial Not Found"
      )
  )
  

• XLOOKUP的优势：无需手动计算行号，逻辑更直观，支持动态数组

注意事项 #

• 确保两个目标工作簿处于打开状态，否则外部引用可能无法正常获取数据
• 序列号唯一，避免出现多匹配导致的结果异常
• 若数据区域范围有变动，需同步调整公式中的区域引用（比如$A$2:$I$1910）

内容的提问来源于stack exchange，提问作者C. Smith