如何在单个Excel函数中结合FILTER与INDEX/MATCH筛选查找最接近值？

现有基础公式 #

原用于筛选公司债券数据集的FILTER公式：

=FILTER($D$13:$V$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000))

需求与尝试 #

原本流程是先用上述公式筛选数据集，再通过INDEX+MATCH+MIN(ABS(...))组合，在筛选结果中查找某列最接近参考值的行，以此定位流动性最优的债券。现在希望将筛选与查找合并为单个函数，已尝试以下公式但未达成目标：

INDEX(FILTER($D$13:$U$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000)),MATCH(MIN(ABS(AQ12:AQ18-G5)),ABS(AQ12:AQ18-G5),0))

可行实现方案 #

方案1：XLOOKUP结合FILTER（Excel 365/2021及以上版本） #

直接用XLOOKUP替代INDEX+MATCH，将筛选结果作为查找范围，在筛选后的数据集内计算差值绝对值的最小值对应行：

=XLOOKUP(MIN(ABS(INDEX(FILTER($D$13:$U$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000)),,COLUMN(AQ12)-COLUMN($D$13)+1)-G5)),ABS(INDEX(FILTER($D$13:$U$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000)),,COLUMN(AQ12)-COLUMN($D$13)+1)-G5),FILTER($D$13:$U$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000)),"无匹配结果")

• 说明：COLUMN(AQ12)-COLUMN($D$13)+1用于计算目标列在筛选区域中的相对列号，确保准确提取筛选后对应列的数据与G5计算差值。

方案2：用LET函数简化逻辑（Excel 365及以上版本） #

通过LET函数定义中间变量，避免重复编写FILTER公式，提升可读性与计算效率：

=LET(
    filtered_data, FILTER($D$13:$U$5000,(($F$13:$F$5000=$X$6)+($F$13:$F$5000=$Y$6))*($T$13:$T$5000>=$X$5)*($T$13:$T$5000<=$Y$5)*(D5=$D$13:$D5000)),
    target_col, INDEX(filtered_data,,COLUMN(AQ12)-COLUMN($D$13)+1),
    diff_abs, ABS(target_col-G5),
    min_diff, MIN(diff_abs),
    XLOOKUP(min_diff, diff_abs, filtered_data, "无匹配结果")
)

• 优势：将筛选结果、目标列、差值绝对值等步骤拆分定义，公式逻辑清晰，修改参数更便捷。

注意事项 #

1. 确保Excel版本支持FILTER、XLOOKUP、LET函数（Excel 365/2021及以上版本），旧版Excel需用数组公式替代。
2. 原流程中的AQ12:AQ18范围已通过相对列号计算替代，确保与筛选结果同步。
3. 若存在多个行差值绝对值相同的情况，XLOOKUP会返回第一个匹配行；如需返回所有匹配结果，可再次结合FILTER筛选。

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