Excel提取指定日期对应Follow-up Number与ID的公式问题求助

问题根源分析 #

原公式返回空白主要有两个核心原因：

• 日期格式不匹配：TEXT函数转换后的文本格式与B/C列的日期值存储格式不一致，导致比较失效。
• COUNTIFS区域逻辑错误：COUNTIFS无法直接对二维区域（$B$2:$C$19）进行条件统计，跨列统计逻辑不成立。

分步修复方案 #

1. 统一日期格式（基础前提） #

选中B、C、H列，右键选择「设置单元格格式」，将格式统一设置为：

• 选择「日期」分类下的「短日期」，或自定义格式为dd-mm-yyyy，确保所有日期的存储与显示格式完全一致。

2. 修正Follow-up Number列（I2）公式 #

替换原公式为以下公式，下拉填充至需要的行：

=IF(SUMPRODUCT(($A$2:$A$19=$A2)*($B$2:$B$19=$H$1)+($A$2:$A$19=$A2)*($C$2:$C$19=$H$1))>0, "Follow-up " & SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$H$1)+($A$2:$A2=$A2)*($C$2:$C2=$H$1)), "")

公式逻辑说明：

• 第一个SUMPRODUCT：统计当前ID在B、C列中匹配查询日期的总次数，判断是否存在匹配项。
• 第二个SUMPRODUCT：统计到当前行为止，该ID匹配查询日期的累计次数，生成对应的Follow-up序号。

3. 保留ID列（J2）公式 #

原公式逻辑正常，可继续使用：

=IF(I2<>"", $A2, "")

进阶方案（Excel 365/2021 动态数组） #

如果使用支持动态数组的Excel版本，可一次性生成所有结果，无需手动下拉：
在I2单元格输入以下公式，自动扩展结果：

=LET(
    data, A2:C19,
    ids, INDEX(data,,1),
    fup1, INDEX(data,,2),
    fup2, INDEX(data,,3),
    query_date, H1,
    matches, (fup1=query_date)+(fup2=query_date),
    filtered_ids, ids*matches,
    non_blank, filtered_ids<>0,
    unique_ids, UNIQUE(FILTER(filtered_ids, non_blank)),
    count_per_id, MAP(unique_ids, LAMBDA(x, SUM((ids=x)*matches))),
    expand_ids, TOCOL(REPT(unique_ids, count_per_id),,1),
    followup_nums, TOCOL(MAP(count_per_id, LAMBDA(n, SEQUENCE(n,,1))),,1),
    HSTACK(expand_ids, "Follow-up "&followup_nums)
)

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