Excel INDIRECT函数动态行多列范围引用#REF错误求助

核心问题分析 #

你遇到的#REF错误，大概率是INDIRECT字符串拼接出错（比如工作表名称的引号处理、行号返回无效值），或是INDIRECT本身的易失性特性导致的计算异常。更稳妥的方式是用非易失性的INDEX函数构建动态范围，替代INDIRECT。

解决方案 #

基础版公式（兼容所有Excel版本） #

假设你用来获取目标行号的MATCH函数是MATCH(你的查找条件, 'TAB1'!A:A, 0)（替换成你实际的查找逻辑），修改后的完整公式如下：

=INDEX('TAB1'!C4:BC4,MATCH(-BM7,INDEX('TAB1'!C:BC, MATCH(你的查找条件, 'TAB1'!A:A, 0), 0),0))

公式拆解

• INDEX('TAB1'!C:BC, 动态行号, 0)：返回目标行号下C到BC列的整行数据，完美替代原公式中固定的'TAB1'!C64:BC64
• 外层MATCH(-BM7, ... ,0)：在动态获取的整行数据中执行匹配
• 最外层INDEX('TAB1'!C4:BC4, ...)：返回最终结果

优化版（Excel 365/2021及以上） #

用LET函数存储动态行号，减少重复计算，让公式更易读：

=LET(
    targetRow, MATCH(你的查找条件, 'TAB1'!A:A, 0),
    lookupRange, INDEX('TAB1'!C:BC, targetRow, 0),
    INDEX('TAB1'!C4:BC4, MATCH(-BM7, lookupRange, 0))
)

若坚持用INDIRECT的修正写法 #

如果一定要用INDIRECT，必须确保字符串拼接完全正确（注意工作表名称的单引号包裹），示例如下：

=INDEX('TAB1'!C4:BC4,MATCH(-BM7,INDIRECT("'TAB1'!C"&MATCH(你的查找条件, 'TAB1'!A:A, 0)&":BC"&MATCH(你的查找条件, 'TAB1'!A:A, 0)),0))

注：这种写法需要两次调用MATCH，效率不如INDEX方案，且易因拼接失误报错。

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