无需VBA：如何按条件跨工作表/工作簿动态查找数据？

核心思路 #

利用Excel的INDIRECT函数动态生成数据源引用，配合文本函数提取年份标识，再结合多条件查找函数（XLOOKUP或INDEX+MATCH）实现自动切换数据源，完全避免硬编码年份判断，新增年份时无需修改公式。

具体实现方案 #

假设你的查找表结构：

• 当前工作表（如汇总表）中：
  • A列：待查找的RMA编号（示例：XYZ/21-12345）
  • B列：对应的10位物料编码
  • C列：要返回的「Finished?」状态

1. 提取年份标识并生成动态引用路径 #

从RMA编号中提取年份后缀（如从XYZ/21提取21，对应Sheet 2021），用SEARCH+MID组合实现：

MID(A2, SEARCH("XYZ/", A2)+4, 2)

• 跨工作场景（源文件名为Claims 20XX.xlsx）：

"[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "!"

• 同工作簿场景：

"Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "!"

2. 多条件查找公式（XLOOKUP版本） #

结合INDIRECT动态引用数据源，用XLOOKUP实现双条件匹配：

=XLOOKUP(1, (INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$A:$A")=A2)*(INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$C:$C")=B2), INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$D:$D"), "未找到")

• 逻辑说明：
  • 用1匹配「RMA编号相等+物料编码相等」的双重条件
  • 返回对应源表D列的「Finished?」状态，无匹配时返回「未找到」

3. 同工作簿简化版公式 #

如果数据源在同一工作簿的Sheet 2021、Sheet 2022等工作表中，公式可简化：

=XLOOKUP(1, (INDIRECT("'Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$A:$A")=A2)*(INDIRECT("'Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$C:$C")=B2), INDIRECT("'Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$D:$D"), "未找到")

4. 旧版Excel兼容方案（INDEX+MATCH） #

若Excel版本不支持XLOOKUP，可使用INDEX+MATCH组合：

=INDEX(INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$D:$D"), MATCH(1, (INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$A:$A")=A2)*(INDIRECT("'[Claims 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & ".xlsx]Sheet 20" & MID(A2, SEARCH("XYZ/", A2)+4, 2) & "'!$C:$C")=B2), 0))

• 注意：旧版Excel中需按Ctrl+Shift+Enter确认数组公式，新版Excel会自动识别。

方案优势 #

• 动态适配：新增年份（如2023）时，只要源文件/工作表命名为Claims 2023.xlsx和Sheet 2023，公式无需修改即可自动适配
• 无硬编码：避免IF嵌套判断年份的繁琐，也不需要辅助列
• 精准匹配：同时验证RMA编号和物料编码，确保数据准确性

注意事项 #

• 源文件/工作表命名需严格遵循Claims 20XX.xlsx和Sheet 20XX格式，否则公式会返回错误
• 跨工作簿查找时，源文件需处于打开状态；若需关闭源文件也能查找，可改用Power Query导入源数据（稳定性更高）
• 若RMA编号的年份标识格式变化（如XYZ-2021），仅需调整MID和SEARCH的参数即可，核心逻辑不变

内容的提问来源于stack exchange，提问作者Nebojša Tumbas