使用溢出范围D3#作为XLOOKUP表数组时返回#N/A的问题求助

问题背景 #

• D4起是通过UNIQUE函数生成的转置溢出股票代码范围
• D3起是对应这些股票的工作表名溢出范围
• 原拖拽公式=XLOOKUP($B9;INDIRECT(D3&"A:A");INDIRECT(D3&"B:B");;FALSE)可正常查询，但改为适配动态溢出的=XLOOKUP($B9;INDIRECT(D3#&"A:A");INDIRECT(D3#&"B:B");;FALSE)后返回#N/A，F9手动求值却能得到正确结果

解决方案 #

使用BYCOL函数遍历溢出的工作表名数组，逐个执行XLOOKUP查询，公式如下：

=BYCOL(D3#, LAMBDA(sheet, XLOOKUP($B9, INDIRECT(sheet&"A:A"), INDIRECT(sheet&"B:B"),,FALSE)))

原理说明 #

• BYCOL(D3#, LAMBDA(sheet, ...))会依次取出D3#溢出区域里的每个工作表名，作为参数传入Lambda表达式
• 对每个工作表单独执行XLOOKUP，避免了直接将数组传入INDIRECT时的解析异常
• 公式会自动溢出返回所有对应股票的查询结果，无需手动拖拽，完全适配D3#的动态扩展

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