求助：Excel中精确匹配代码+非精确匹配日期的MATCH函数用法

我试过MATCH/INDEX、VLOOKUP、SUMPRODUCT、AGGREGATE等多个Excel函数，都没得到想要的结果，希望能拿到最优解决方案。

需求：根据A列的精确代码，以及指定日期，找到对应代码下日期小于等于该日期的最近值所在的行号。

我现有一个可实现双精确匹配的公式：

=MATCH(1,(("B"=A:A)*(2005=B:B)),0)

这个公式能正确返回第6行，但无法调整为适用于「Code=B、Year=2007」的场景——此时需要返回Code=B且年份最接近2007的较小值对应的第6行。我尝试的无效公式如下：

=SUMPRODUCT(MATCH(1,(A:A="B")*(B:B<=2007),0))

解决方案 #

方法1：适用于Excel 365/2021（动态数组版本）

使用XLOOKUP结合反向查找，直接定位符合条件的最新行：

=XLOOKUP(1,(A:A="B")*(B:B<=2007),ROW(A:A),,,-1)

• 逻辑：(A:A="B")*(B:B<=2007)生成符合「代码为B且年份≤2007」的布尔数组；
• XLOOKUP最后一个参数-1表示从后往前查找第一个匹配项，即取符合条件的最大行号（对应最近的日期）。

方法2：兼容旧版Excel（无需动态数组）

方案A：数组公式（需按Ctrl+Shift+Enter确认）

用MAX筛选符合条件的行号，直接取最大值：

=MAX(IF((A:A="B")*(B:B<=2007),ROW(A:A),0))

方案B：用AGGREGATE避免数组快捷键

=AGGREGATE(14,6,ROW(A:A)/((A:A="B")*(B:B<=2007)),1)

• 逻辑：ROW(A:A)/((A:A="B")*(B:B<=2007))会将不符合条件的行转为错误值；
• AGGREGATE(14,6,...)表示忽略错误值，取第1大的行号（即符合条件的最新行）。

原公式无效原因 #

你用的SUMPRODUCT(MATCH(1,(A:A="B")*(B:B<=2007),0))中，MATCH默认是正向查找第一个匹配项，会返回最早符合「B且≤2007」的行号，而非你需要的最近（最大）行号，因此无法满足需求。

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