Excel按类别动态取值问题：NAV表公式下拉未正确更新

原公式核心问题 #

1. INDEX数组取值错误：你用COLUMN(DATA!$H$4:$N$566)-7作为INDEX的数据源，这返回的是列的相对序号（H列对应1、I列对应2…），而非DATA表中单元格的实际内容（AAA、BBB等），这是取值错误的根本原因。
2. AGGREGATE逻辑冗余且方向偏差：原公式试图用AGGREGATE筛选符合类别的列，但实际只需精准定位类别对应的列即可，AGGREGATE的用法在这里反而增加了复杂度，且未关联到实际数据行。

修正方案 #

根据你的需求（G7输入类别后，G9及以下返回对应列的逐行数据），替换为以下公式：

=INDEX(DATA!$H:$N,ROWS(G$9:G9)+3,MATCH(G$7,DATA!$H$3:$N$3,0))

公式解释 #

• MATCH(G$7,DATA!$H$3:$N$3,0)：精准定位G7中的类别在DATA表第3行（$H$3:$N$3）对应的列位置，返回1-7的数字（对应H到N列）。
• ROWS(G$9:G9)+3：下拉时自动生成1、2、3…的序列，加3是因为数据从DATA表第4行开始（1+3=4、2+3=5…），对应目标行号。
• INDEX(DATA!$H:$N,行号,列号)：根据行号和列号提取DATA表中对应单元格的内容。

扩展说明 #

如果DATA表中存在多个列属于同一类别（比如有两列都是PER），需要依次提取所有列的所有数据，可使用以下公式（保留AGGREGATE但修正逻辑）：

=INDEX(DATA!$H$4:$N$566,ROWS(G$9:G9)-MOD(ROWS(G$9:G9)-1,COUNTA(DATA!$H$4:$H$566))+1,AGGREGATE(15,6,(COLUMN(DATA!$H$3:$N$3)-COLUMN(DATA!$H$3)+1)/(DATA!$H$3:$N$3=G$7),ROUNDUP(ROWS(G$9:G9)/COUNTA(DATA!$H$4:$H$566),0)))

这个公式会先提取第一个符合条件列的所有行，再提取第二个符合条件列的所有行，以此类推。

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