Google Sheets如何查找当前单元格上方最近匹配值并返回同行其他列数据

适用前提 #

默认数据结构：第1行为表头，A列为判断列，B列为取值源列，C列为Results结果列，目标匹配值为x，可根据实际场景替换对应参数。 #

方案1：下拉填充公式 #

将以下公式粘贴到C2单元格，下拉填充到所有需要计算的行即可：
=IF(A2="x", B2, LOOKUP(1, 0/(A$1:A1="x"), B$1:B1))

公式逻辑 #

• 第一层IF判断当前行A列值是否为x，匹配则直接返回同行B列值
• 不匹配时，LOOKUP函数会在A列从表头到当前行上一行的范围内，定位最后一个值为x的行，返回对应B列的结果，即为距离当前行最近的上方匹配值

方案2：自动溢出数组公式 #

将以下公式粘贴到C2单元格，无需手动下拉，会自动匹配所有A列有数据的行生成结果：
=BYROW(SEQUENCE(COUNTA(A2:A)), LAMBDA(k, LET(r, A2:INDEX(A:A, k+1), IF(INDEX(r, k)="x", INDEX(B2:B, k), LOOKUP(1, 0/(OFFSET(A2,0,0,k-1)="x"), OFFSET(B2,0,0,k-1)))))) #

参数调整说明 #

• 公式中的"x"替换为实际需要匹配的A列目标值，文本类型需加英文引号，数字类型无需加引号
• 若数据起始行不是第2行，对应调整公式中的行号即可：例如数据从第3行开始，将方案1公式中的A$1:A1改为A$2:A2、B$1:B1改为B$2:B2
• 若需处理首行数据无匹配的#N/A报错，可在公式外层套IFERROR函数，示例：=IFERROR(原公式, "无前置匹配值")

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