如何在Google Sheets中匹配获取员工晋升后对应时段的最新职位

前置说明 #

以下操作默认两个表结构如下，你可以根据自己的实际列位置调整公式中的列号：

• 辅助表（Helper Sheet）：A列为晋升生效日期，B列为员工姓名，C列为对应职位
• 主表（Main Sheet）：A列为工时记录日期，B列为员工姓名，D列为待填充的职位列，第一行为表头

操作步骤 #

方案1：XLOOKUP公式（推荐，新版Google Sheets通用） #

直接在主表D2单元格输入如下公式，下拉填充到所有行即可：
=XLOOKUP(1, (Helper!B:B=B2) * (Helper!A:A<=A2), Helper!C:C, "无匹配职位", 0, -1)
参数说明：

• 第二个参数的两个判断条件，分别限定匹配同姓名、且辅助表生效日期早于等于当前工时记录日期
• 最后一个参数-1表示从下往上查找满足条件的记录，自动取最新生效的职位，刚好适配无记录月份自动继承上一个有效职位的需求
  如果需要整列自动填充不用手动下拉，可改用溢出数组公式：
  =ARRAYFORMULA(IF(A2:A="",,XLOOKUP(1, (Helper!B:B=B2:B) * (Helper!A:A<=A2:A), Helper!C:C, "无匹配职位", 0, -1)))

方案2：INDEX+MATCH组合（兼容旧版Google Sheets） #

先提前将辅助表按「员工姓名升序、生效日期升序」排序后，在主表D2单元格输入如下公式，下拉填充即可：
=INDEX(Helper!C:C, MATCH(1, (Helper!B:B=B2) * (Helper!A:A<=A2), 1))

注意事项 #

• 两个表的日期列需统一设置为日期格式，不要存储为文本，否则日期比对会失效
• 员工姓名需保持完全一致，避免多余空格、大小写差异，若需要兼容格式差异，可将姓名匹配逻辑改为(TRIM(LOWER(Helper!B:B))=TRIM(LOWER(B2)))

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