Google Sheets中ARRAYFORMULA实现自适配倒序VLOOKUP问题求助

问题描述 #

尝试通过SORT反转序列实现自下而上VLOOKUP，存在两个问题：

1. 逐行公式（对应截图H列）偶尔失效，始终返回空值：

=IF(E3="","",VLOOKUP("",ARRAYFORMULA(SUBSTITUTE(QUERY(SORT({SEQUENCE(COUNTIF(INDIRECT("A2:A"&row()),"<>''")),{INDIRECT("B2:B"&row()),INDIRECT("A2:A"&row())}},1,FALSE),"Select Col2, Col3"),"","§")),2,FALSE))

2. ARRAYFORMULA批量公式（对应截图I列）始终返回第一个值，无法得到F列（绿色标注）的预期结果：

=ARRAYFORMULA(IF(E3:E="","",VLOOKUP("",ARRAYFORMULA(SUBSTITUTE(QUERY(SORT({SEQUENCE(COUNTIF(INDIRECT("A2:A"&row(E3:E)),"<>''")),{INDIRECT("B2:B"&row(E3:E)),INDIRECT("A2:A"&row(E3:E))}},1,FALSE),"Select Col2, Col3"),"","")),2,FALSE))

需求：实现截图中F列的预期结果，优先使用ARRAYFORMULA，需自动适配新增行。

问题根源 #

• 逐行公式中INDIRECT结合COUNTIF的动态范围计算易因空行、序列生成错误导致失效；
• 批量公式中row(E3:E)返回数组，但INDIRECT不支持数组参数，导致所有行复用同一个范围，返回相同结果。

解决方案 #

方案1：全局最后一个非空值匹配 #

如果需求是每行E列非空时，返回A列最后一个非空单元格对应的B列值，用以下公式：

=ARRAYFORMULA(IF(E3:E="","",LOOKUP(2,1/(A2:A<>"")+0,B2:B)))

说明：

• 1/(A2:A<>"")+0 将A列非空单元格转为1，空单元格转为错误值；
• LOOKUP(2, ..., B2:B) 会忽略错误值，定位到最后一个1对应的B列值，实现自下而上匹配，且自动适配新增行。

方案2：向上匹配最近非空值 #

如果需求是每行E列非空时，向上找到最近的A列非空单元格对应的B列值，用BYROW结合动态范围：

=ARRAYFORMULA(IF(E3:E="","",BYROW(ROW(E3:E),LAMBDA(r,LOOKUP(2,1/(A2:INDEX(A:A,r)<>""),B2:INDEX(B:B,r))))))

说明：

• ROW(E3:E) 获取当前行号，INDEX(A:A,r) 构建从A2到当前行的动态范围；
• LOOKUP 在该范围内定位最后一个非空A列对应的B列值，无需INDIRECT，稳定性更高，支持自动新增行。

方案3：简化版逐行兼容公式 #

如果仍需保留逐行公式的逻辑，替换为更稳定的写法：

=IF(E3="","",LOOKUP(2,1/(A2:A3<>""),B2:B3))

说明：直接限定范围到当前行，避免INDIRECT和SEQUENCE带来的计算误差，批量使用时可下拉，或结合ARRAYFORMULA用方案2替代。

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