Google Sheets中查找指定名称下早于给定日期且在限定天数内的最近匹配日期

看起来你是想在Google Sheets里实现一个实用的日期匹配功能——给定目标名称、参考日期和天数限制，找出该名称下早于参考日期且在限定天数范围内的最近日期对吧？你的原公式返回了第一个匹配项，是因为逻辑上有几个小细节没处理好，我来帮你修正并解释清楚。

先明确你的核心需求 #

我们需要同时满足三个条件：

• A列的名称必须和C2单元格的目标名称完全匹配
• B列的日期必须不晚于C3单元格的参考日期
• 参考日期与B列日期的天数差必须不超过C4单元格的天数限制
  最终要返回满足以上条件的日期中，和参考日期最接近的那一个。

原公式的问题分析 #

你的原公式ArrayFormula(INDEX(B1:B, MATCH(MIN(IF((A1:A=C1)*(B1:B<=C2)*(C2-B1:B<=20), C2-B1:B, 999999)), 0)))存在几个问题：

1. 单元格引用错误：A1:A=C1应该对应C2的名称，B1:B<=C2应该对应C3的参考日期
2. 硬编码天数：C2-B1:B<=20里的20固定死了，无法使用C4的动态天数限制
3. MATCH逻辑缺失：只匹配了最小差值的位置，但没有关联名称和日期的双重条件，导致可能匹配到其他名称的无效数据

修正后的公式（两种写法） #

写法一：基于INDEX+MATCH的修正版

=ARRAYFORMULA(INDEX(B:B, MATCH(MIN(IF((A:A=C2)*(B:B<=C3)*(C3-B:B<=C4), C3-B:B, 999999)), IF((A:A=C2)*(B:B<=C3)*(C3-B:B<=C4), C3-B:B, 999999), 0)))

这个公式的逻辑拆解：

• 用IF((A:A=C2)*(B:B<=C3)*(C3-B:B<=C4), C3-B:B, 999999)遍历所有行：满足三个条件的行计算日期差值，不满足的返回一个极大数（确保不会被MIN选中）
• MIN(...)筛选出符合条件的最小差值（也就是最接近参考日期的日期）
• MATCH函数在差值数组中找到这个最小差值的精确位置
• 最后用INDEX从B列取出对应位置的日期

写法二：更简洁的XLOOKUP版（推荐）

如果你的Google Sheets支持XLOOKUP函数，这个写法更直观易懂：

=ARRAYFORMULA(XLOOKUP(TRUE, (A:A=C2)*(B:B<=C3)*(C3-B:B<=C4), B:B, , 0, -1))

逻辑解释：

• (A:A=C2)*(B:B<=C3)*(C3-B:B<=C4)生成一个布尔数组，满足条件的位置为TRUE
• XLOOKUP的最后一个参数-1表示从后往前查找，这样会直接返回符合条件的最后一个（也就是日期最近的）匹配项，完美契合你的需求

测试你的示例场景 #

当C2是john、C3是3/30/2023、C4是20时：

• 符合条件的john的日期有3/27/2023和3/29/2023
• 两个公式都会返回3/29/2023，和你预期的C5结果一致

备注：内容来源于stack exchange，提问作者MMsmithH