如何修改TEXTJOIN+FILTER公式，下拉时列范围从B2:B21递增？

修改后的公式（推荐稳定版） #

=TEXTJOIN(", ",TRUE,FILTER('Staff Rota'!$A$2:$A$21,(INDEX('Staff Rota'!$2:$21,0,ROW())="W")*(INDEX('Staff Rota'!$2:$2,0,ROW())=D2)))

原理说明 #

• 动态列范围生成：INDEX('Staff Rota'!$2:$21,0,ROW())是核心逻辑，ROW()返回当前公式所在行的行号。公式在第2行时，ROW()=2，对应B列（列号2），等价于'Staff Rota'!B2:B21；向下拖拽到第3行时，ROW()=3，自动切换为C列范围'Staff Rota'!C2:C21，以此实现列的递增。
• 匹配条件同步：原公式中'Staff Rota'!B2=D2的判断，用INDEX('Staff Rota'!$2:$2,0,ROW())替换，确保取对应列的第2行数据与D2匹配，和动态列范围保持同步。
• 完全保留原公式的FILTER筛选、TEXTJOIN文本合并逻辑，功能与原需求一致。

起始行适配调整 #

如果公式不是从第2行开始（比如起始行为第5行），只需调整ROW()的偏移量。例如起始行是第5行，将公式中的ROW()替换为ROW()-3（5-3=2，对应B列），修改后公式：

=TEXTJOIN(", ",TRUE,FILTER('Staff Rota'!$A$2:$A$21,(INDEX('Staff Rota'!$2:$21,0,ROW()-3)="W")*(INDEX('Staff Rota'!$2:$2,0,ROW()-3)=D2)))

可选易失性方法（不推荐） #

若想用OFFSET实现，功能相同但OFFSET是易失性函数（工作表任何变化都会触发重算），大表格中可能影响性能：

=TEXTJOIN(", ",TRUE,FILTER('Staff Rota'!$A$2:$A$21,(OFFSET('Staff Rota'!$B$2:$B$21,0,ROW()-2)="W")*(OFFSET('Staff Rota'!$B$2,0,ROW()-2)=D2)))

关于INDEX+MATCH未解决的可能原因 #

大概率是没有将整个条件引用范围通过INDEX动态化，比如仅替换了单个单元格引用，未处理B2:B21这类整列范围，导致拖拽时列号无法正确递增。

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