嘿，这个问题问得特别戳中痛点！很多刚摸透INDEX函数皮毛的朋友都会被它的两种“身份”搞懵，我来给你掰扯清楚～

首先得明确：INDEX确实有两种工作模式——数组形式（返回值）和引用形式（返回单元格地址），而它到底用哪种，完全取决于它所处的上下文环境，也就是嵌套它的函数或者运算符需要什么。

拿你提到的例子来说：

=SUM(A2:INDEX(A:A, ROWS(A:A)))

这里的INDEX是跟在冒号:后面的，而Excel里的冒号是用来定义单元格区域的，它需要的是单元格引用（地址），不是具体的值。所以INDEX自动切换到了引用模式，返回的是A列第1048576行的单元格引用（也就是A1048576），这样A2:INDEX(...)就变成了完整的区域A2:A1048576，SUM自然就能正常计算这个范围的数值了。

反过来，如果你单独写=INDEX(A:A, ROWS(A:A))，这时候Excel需要的是一个具体的结果值，INDEX就会切换到数组模式，返回A1048576单元格里的内容（空单元格的话就是空值或者0）。

再看你自己改造的公式：

=UNIQUE(FILTER('SheetName'!D2:INDEX('SheetName'!D:D,ROWS('SheetName'!D:D)),ISTEXT('SheetName'!D2:INDEX('SheetName'!D:D,ROWS('SheetName'!D:D)))))

这里的INDEX同样是作为区域的一部分（D2:INDEX(...)），而FILTER函数的第一个参数需要的是一个单元格区域引用，所以INDEX再次切换到引用模式，返回D列最后一行的引用，形成D2:D1048576的完整范围，这样FILTER才能遍历这个区域里的每一个单元格，筛选出文本内容后再去重。

总结一下判断逻辑：

• 当INDEX的第一个参数是单元格区域引用（比如A:A、B2:C10），并且它被用在需要引用的场景里（比如作为区域的一部分、作为需要引用的函数参数），就会返回单元格引用；
• 当它被用在需要具体值的场景里（比如单独作为公式结果、嵌套在需要数值的函数中），就会返回单元格里的内容。

说白了，INDEX就像个“变色龙”，会根据周围的需求调整自己的输出，这也是它灵活但容易让人困惑的原因～

备注：内容来源于stack exchange，提问作者JNeilson