Excel公式需求：匹配ID后按日期筛选关联唯一最近数值

核心需求回顾 #

为Table B的newnumber列（K列）编写公式，需同时满足：

• 匹配Table A与Table B的ID
• Table A对应行日期 ≥ Table B当前行日期
• Table A的TYPE字段以"EX"结尾
• 从Table A的number列（D列）中选取与Table B的base列（J列）最接近的数值，且每个number仅能被使用一次

解决公式（适用于Excel 365/2021及以上版本） #

在K2单元格输入以下公式，下拉填充即可：

=LET(
    匹配ID, TableA[ID]=@TableB[ID],
    日期符合, TableA[日期]>=@TableB[日期],
    TYPE符合, RIGHT(TableA[TYPE],2)="EX",
    未被使用, COUNTIF($K$1:K1, TableA[number])=0,
    候选数值, FILTER(TableA[number], 匹配ID*日期符合*TYPE符合*未被使用, ""),
    IF(COUNTA(候选数值)=0, "", XLOOKUP(1, 1/(ABS(候选数值-@TableB[base])=MIN(ABS(候选数值-@TableB[base]))), 候选数值, "", 0, 1))
)

公式拆解说明 #

1. LET函数：定义变量简化公式，避免重复计算，提升可读性
2. 匹配ID/日期符合/TYPE符合：分别筛选满足基础条件的TableA行，其中RIGHT(TableA[TYPE],2)="EX"精准判断TYPE以"EX"结尾，替代之前易出错的ISNUMBER(SEARCH(...))（后者会匹配包含EX的任意字段）
3. 未被使用：通过COUNTIF($K$1:K1, TableA[number])=0统计当前行以上已使用的number，确保每个数值仅被匹配一次
4. 候选数值：用FILTER聚合所有符合条件的未使用number，无符合项则返回空文本
5. 最接近值匹配：通过计算候选值与base的绝对值差，找到最小差值对应的数值，用XLOOKUP返回结果

之前公式返回N/A的常见原因 #

• 使用ISNUMBER(SEARCH("EX", TableA[TYPE]))会误匹配包含EX的TYPE（如"EXAMPLE"），而非仅以EX结尾的字段
• 未加入"数值仅能被使用一次"的判断逻辑，导致筛选范围错误
• 未处理候选数值为空的情况，直接返回匹配结果导致N/A

示例情况解释 #

• K5无值：符合条件的number已被前面的行占用，未被使用条件筛选后候选数值为空，返回空文本
• K6无值：TYPE不符合"以EX结尾"的要求，且无其他可用候选数值，返回空文本

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