如何用Excel的INDEX/MATCH等函数基于平均值规则实现近似匹配查询

架构老林

2026-10-4

Excel区间按相邻值平均值匹配公式方案

适用条件

参考区间A列需为升序排列
匹配规则：无精确匹配时，输入值小于相邻上下A值的平均值返回下界B值，大于则返回上界B值，精确匹配直接返回对应B值

公式方案

Excel 365/2021及以上版本（精简写法）

用LET+XLOOKUP组合，逻辑清晰易维护：

=LET(
    lower_a, XLOOKUP(C1,A:A,A,,1),
    lower_b, XLOOKUP(C1,A:A,B,,1),
    upper_a, XLOOKUP(C1,A:A,A,,2),
    upper_b, XLOOKUP(C1,A:A,B,,2),
    IF(lower_a=upper_a, lower_b, IF(C1<(lower_a+upper_a)/2, lower_b, upper_b))
)

兼容所有Excel版本（INDEX+MATCH组合）

符合常用查询函数使用需求，无需高版本支持：

=IF(
    COUNTIF(A:A,C1)>0,
    VLOOKUP(C1,A:B,2,FALSE),
    IF(
        C1<(INDEX(A:A,MATCH(C1,A:A,1))+INDEX(A:A,MATCH(C1,A:A,1)+1))/2,
        INDEX(B:B,MATCH(C1,A:A,1)),
        INDEX(B:B,MATCH(C1,A:A,1)+1)
    )
)

边界异常处理补充

如果需要处理输入值小于A列最小值、大于A列最大值的异常场景，可使用带边界判断的兼容版本：

=IFERROR(
    IF(
        COUNTIF(A:A,C1)>0,
        VLOOKUP(C1,A:B,2,FALSE),
        IF(
            C1<(INDEX(A:A,MATCH(C1,A:A,1))+INDEX(A:A,MATCH(C1,A:A,1)+1))/2,
            INDEX(B:B,MATCH(C1,A:A,1)),
            INDEX(B:B,MATCH(C1,A:A,1)+1)
        )
    ),
    IF(C1<MIN(A:A),B1,IF(C1>MAX(A:A),INDEX(B:B,COUNT(A:A)),""))
)