跨年份动态提取对应月份日期的Excel函数失效问题求助

问题根源 #

你当前使用的公式=INDEX(Data!$K$2:$K$366,MATCH(Data!J2,Data!$J$2:$J$366,0),1)仅能返回第一个匹配指定月份的日期，因为MATCH函数默认只查找列表中第一个符合条件的项，无法批量提取同月份的所有日期；且下拉时未针对当前行的目标月份做动态匹配调整，导致后续月份无法正常提取。

解决方案 #

根据你的Excel版本，选择以下适配方案：

方案1：Excel 365/2021（支持动态数组） #

使用FILTER函数一次性提取对应月份的所有日期，结果自动溢出到下方单元格，无需手动下拉：

=FILTER(Data!$K$2:$K$366,TEXT(Data!$K$2:$K$366,"mmmm")=A2)

• 说明：
  • TEXT(Data!$K$2:$K$366,"mmmm")将日期转换为完整英文月份名称（如"January"），与目标单元格A2的月份匹配。
  • 跨年份的同月份日期会被一并提取，无需额外处理年份逻辑。
  • 如果Data!J列已存储月份名称，可直接替换为Data!$J$2:$J$366=A2，提升计算效率：

    =FILTER(Data!$K$2:$K$366,Data!$J$2:$J$366=A2)
    

方案2：旧版Excel（不支持动态数组） #

使用数组公式逐个提取日期，在目标单元格（如B2）输入以下公式后，按Ctrl+Shift+Enter确认数组公式，再下拉填充：

=IFERROR(INDEX(Data!$K$2:$K$366,SMALL(IF(TEXT(Data!$K$2:$K$366,"mmmm")=$A2,ROW(Data!$K$2:$K$366)-ROW(Data!$K$2)+1),ROWS($B$2:B2))),"")

• 说明：
  • IF(TEXT(...)=$A2,...)标记所有月份匹配的日期位置。
  • SMALL(...,ROWS($B$2:B2))依次提取第1、第2...第N个符合条件的日期位置，实现逐个提取。
  • IFERROR用于隐藏无匹配日期时的#NUM!错误，返回空值。
  • 若Data!J列已存月份名称，替换TEXT(...)为Data!$J$2:$J$366=$A2即可。

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