匹配姓名与动态日期列反向查找指定值最近出现日期的公式实现

适用Excel 365/2021及以上版本 #

先定义通用引用规则，你可以根据自己的实际表格范围调整对应参数：

• 姓名列：A列，表头为第1行
• 日期表头：从B1开始向右排列
• 存放指定姓名的单元格：I1
• 存放指定日期的单元格：I2
• 目标查找值："RNR"

输入以下公式即可直接得到结果：

=LET(
    t_row, XMATCH(I1, A:A),
    t_col, XMATCH(I2, 1:1),
    check_range, INDEX(B:B,t_row):INDEX(1:1,t_col,t_row),
    date_range, B1:INDEX(1:1,t_col),
    XLOOKUP("RNR", check_range, date_range, "无匹配记录", 0, -1)
)

适用2019及更早旧版本Excel #

使用数组公式实现，输入完成后需要按Ctrl+Shift+Enter三键确认才会生效：

=LOOKUP(2,1/(INDEX(B:G,MATCH(I1,A:A,0),1):INDEX(B:G,MATCH(I1,A:A,0),MATCH(I2,1:1,0))="RNR"),B1:INDEX(1:1,MATCH(I2,1:1,0)))

效果验证 #

匹配你给出的示例场景：

• 指定姓名为Bob、指定日期为2021/7/5时，公式返回2021/7/3，符合预期
• 指定姓名为Joe、指定日期为2021/7/4时，公式返回2021/7/1，符合预期

注意事项 #

• 日期表头必须为标准日期格式，不能是文本类型，否则会出现匹配失败的问题
• 如果不需要包含指定日期列本身的查找，把公式中MATCH(I2,1:1,0)的部分改为MATCH(I2,1:1,0)-1即可
• 如果没有匹配的RNR记录，高版本公式会返回无匹配记录，旧版本会返回#N/A，你可以根据需要调整返回值

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