Google Sheets中按非空行号匹配跨表对应值的优化方法

简化公式实现 #

在Sheet1的B2单元格输入以下公式，无需在Sheet2添加辅助列即可直接实现需求：

=ARRAYFORMULA(IF(A2:A<>"", INDEX(Sheet2!A2:A, COUNTIF(A$2:A2, "<>")), ""))

公式原理拆解 #

• ARRAYFORMULA：实现整列批量计算，无需手动下拉公式
• IF(A2:A<>"", ..., "")：仅当Sheet1的A列单元格非空时返回匹配值，空行保持空白
• COUNTIF(A$2:A2, "<>")：统计从A2到当前行的非空单元格数量，得到当前非空值的序号（A$2固定起始行，确保累计计数准确）
• INDEX(Sheet2!A2:A, 序号)：从Sheet2的A2开始的序列中，取出对应序号的单元格内容

效果验证 #

对应测试数据：

• Sheet1 A2（john）：COUNTIF(A$2:A2, "<>")返回1，INDEX取Sheet2 A2的blue
• Sheet1 A3（mary）：COUNTIF(A$2:A3, "<>")返回2，INDEX取Sheet2 A3的green
  完全符合期望的匹配结果。

备选简洁写法（适用于Sheet1非空行连续的场景） #

如果Sheet1的A列非空值是连续无间隔的，也可以使用以下公式：

=ARRAYFORMULA(IF(A2:A<>"", INDEX(FILTER(Sheet2!A2:A, Sheet2!A2:A<>""), SEQUENCE(COUNTA(A2:A))), ""))

• FILTER(Sheet2!A2:A, Sheet2!A2:A<>"")：提取Sheet2 A列所有非空值组成新序列
• SEQUENCE(COUNTA(A2:A))：生成与Sheet1非空值数量一致的连续序号
• INDEX按序号匹配对应值

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