Excel中INDIRECT嵌套公式报错，粘贴字符串转公式却正常的技术问题

你的公式里INDIRECT函数的使用逻辑错误——它的作用是引用单元格/区域，而非直接执行公式文本。你通过CONCAT拼接出来的是一个MATCH公式的字符串，INDIRECT无法解析这种公式表达式，只会把它当作单元格引用路径，自然返回#REF!；而手动加=后是让Excel直接执行这个公式字符串，所以能正常运行。

不需要用INDIRECT+CONCAT绕弯路，直接用数组公式或新版Excel函数就能实现需求：

步骤1：提取唯一父类到Final List的A列 #

在Final List的A2单元格输入：

=UNIQUE(AllData!$A:$A)

这会自动提取AllData里所有不重复的父类，无需手动输入。

步骤2：横向排列对应子类（去重） #

在Final List的B2单元格输入以下公式，然后向右、向下填充：

=IFERROR(INDEX(UNIQUE(FILTER(AllData!$B:$B,AllData!$A:$A=$A2)),COLUMN(A:A)),"")

公式说明： #

• FILTER(AllData!$B:$B,AllData!$A:$A=$A2)：筛选出当前父类对应的所有子类
• UNIQUE(...)：对筛选结果去重
• INDEX(...,COLUMN(A:A))：依次提取去重后子类的第1、2、3...个值
• IFERROR(...)：当没有更多子类时返回空值

兼容旧版Excel的方案（无UNIQUE/FILTER） #

如果用的是旧版Excel，在B2单元格输入数组公式（输入后按Ctrl+Shift+Enter确认）：

=IFERROR(INDEX(AllData!$B:$B,SMALL(IF(AllData!$A:$A=$A2,ROW(AllData!$A:$A)),COLUMN(A:A))),"")

要实现去重的话，需要嵌套MATCH判断是否已出现过：

=IFERROR(INDEX(AllData!$B:$B,SMALL(IF(AllData!$A:$A=$A2,IF(COUNTIF($B$1:B1,AllData!$B:$B)=0,ROW(AllData!$A:$A))),COLUMN(A:A))),"")

同样按Ctrl+Shift+Enter作为数组公式使用。

如果一定要保留你的原逻辑，需要用EVALUATE函数来执行公式文本，但它需要通过定义名称实现：

1. 点击公式选项卡→定义名称→新建名称，比如命名为GetMatch
2. 在“引用位置”输入：=EVALUATE(CONCAT("MATCH(A",ROW(A2),",'AllData'!$B$",H2+2,"$B$100,0)"))
3. 然后在H3单元格输入：=IFERROR(GetMatch + H2, 0)

不过这种方法不如直接用数组公式或新版函数简洁，不推荐。

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