求解可在ARRAYFORMULA中运行的查找指定文本下一行的公式

我在搭建时间跟踪表单及配套电子表格，目前基础功能已经开发完成，核心逻辑是定位到对应用户名的下一条记录，计算用户在特定状态的停留时长。

最初使用的公式如下：

=ArrayFormula(iferror(INDEX($A2:$A,SMALL(IF(B2=$B3:$B,ROW($B$2:$B)),1)), NOW()))

这个公式没法在ARRAYFORMULA里实现自动批量计算，我先后试了三个替代方案都没成功：

• 方案1：

=ARRAYFORMULA(VLOOKUP(B2:B, {INDIRECT("B"&ROW(A2:A)+1&":B"), INDIRECT("A"&ROW(A2:A)+1&":A")}, 2, FALSE))

失效原因：用到了INDIRECT函数，和ARRAYFORMULA的数组批量计算逻辑不兼容。

• 方案2：

=ARRAYFORMULA(SORTN(FILTER(A3:A, B3:B=B2), 1))

失效原因：FILTER和SORTN组合无法在ARRAYFORMULA中逐行迭代计算。

• 方案3：

=ARRAYFORMULA(QUERY(A3:C, "SELECT MIN(A) WHERE B = '"&$B2&"' label MIN(A) ''"))

失效原因：QUERY的拼接条件无法在数组模式下逐行匹配对应行的用户名。

上面所有公式手动下拉填充的时候都能正常跑，但我不想每隔几小时就开表格手动拉公式，要一个能自动批量生效的公式方案，我准备了公开的测试表格用于调试公式效果。

直接在计算列的第2行（第一条数据对应的计算单元格）输入下面的数组公式即可，不需要手动下拉，新数据写入时会自动完成全量计算：

=ARRAYFORMULA(IF(A2:A="",,IFERROR(VLOOKUP(ROW(B2:B),QUERY({ROW(B3:B),A3:A,B3:B},"select Col1,Col2 where Col3 is not null order by Col1"),2,1),NOW())))

公式逻辑说明 #

• 先判断A列的时间记录是否为空，空行直接留空，避免无意义计算
• 用QUERY把第三行开始的行号、时间值、用户名整合成排序后的对照表，按行号升序排列
• 用近似匹配模式的VLOOKUP，定位到当前行之后第一个匹配到用户名的记录行，取对应的时间作为当前状态的结束时间
• 如果匹配不到后续记录，说明用户当前还处在该状态，直接返回NOW()作为临时结束时间，用来计算实时的状态时长
• 整个公式没有用到INDIRECT、逐行FILTER这类不兼容数组批量计算的函数，表单提交新数据时会自动触发计算，完全不需要手动操作。

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