Google Sheets实现自动匹配数据并获取前后值的动态公式需求

实现自动扩展的公式 #

Previous列（A3单元格） #

输入后自动向下填充所有有效行：

=BYROW(B3:B, LAMBDA(val, IF(val="",, INDEX(E:J, ROW(val), IFERROR(MATCH(val, INDIRECT("E"&ROW(val)&":J"&ROW(val)), 0)-1, )))))

Next列（C3单元格） #

输入后自动向下填充所有有效行：

=BYROW(B3:B, LAMBDA(val, IF(val="",, INDEX(E:J, ROW(val), IFERROR(MATCH(val, INDIRECT("E"&ROW(val)&":J"&ROW(val)), 0)+1, )))))

公式解析 #

• BYROW(B3:B, LAMBDA(...))：逐行遍历current列（B3:B），对每个单元格值执行逻辑
• IF(val="",, ...)：空值跳过，避免无效填充
• INDIRECT("E"&ROW(val)&":J"&ROW(val))：动态定位当前行的DATA2区域（E-J列）
• MATCH(val, ..., 0)：精准匹配current值在当前行DATA2中的列位置
• INDEX(..., ±1)：取匹配位置的左侧（-1）或右侧（+1）数值
• IFERROR：处理值未找到的情况，返回空而非报错

兼容旧版的替代方案 #

若你的 Sheets 版本不支持BYROW，用以下数组公式：

Previous列：

=ArrayFormula(IF(B3:B="",, INDEX(E3:J, ROW(B3:B)-ROW(B3)+1, IFERROR(MATCH(B3:B, E3:J, 0)-1, ))))

Next列：

=ArrayFormula(IF(B3:B="",, INDEX(E3:J, ROW(B3:B)-ROW(B3)+1, IFERROR(MATCH(B3:B, E3:J, 0)+1, ))))

注意点 #

• 若DATA2的实际列范围不是E-J，替换公式中的列标即可
• 若同一行DATA2存在多个匹配值，公式会取第一个匹配的位置

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