如何使用XLOOKUP在同一公式中查找大于或小于搜索值的最接近项

架构老林

2026-9-27

Excel查找与搜索值最接近的任意侧数值解决方案

基于XLOOKUP的兼容方案（保留原有使用习惯）

你可以直接对已有的两个XLOOKUP返回结果做差值对比，取绝对值更小的结果即可，以下是使用LET函数优化可读性的版本（适用于Excel 365/2021）：

=LET(
  搜索值,G4,
  查找区域,sheet2!$K$3:$K$534,
  返回区域,sheet2!$L$3:$L$534,
  较小接近值,XLOOKUP(搜索值,查找区域,返回区域,,-1),
  较大接近值,XLOOKUP(搜索值,查找区域,返回区域,,1),
  IF(ABS(搜索值-较小接近值)<=ABS(搜索值-较大接近值),较小接近值,较大接近值)
)

不支持LET的版本可以直接使用嵌套公式：
=IF(ABS(G4-XLOOKUP(G4,sheet2!$K$3:$K$534,sheet2!$L$3:$L$534,,-1))<=ABS(G4-XLOOKUP(G4,sheet2!$K$3:$K$534,sheet2!$L$3:$L$534,,1)),XLOOKUP(G4,sheet2!$K$3:$K$534,sheet2!$L$3:$L$534,,-1),XLOOKUP(G4,sheet2!$K$3:$K$534,sheet2!$L$3:$L$534,,1))

你给出的示例中，搜索值34512.95与较小接近值34509.7的差值为3.25，与较大接近值34527.9的差值为14.95，公式会自动返回34509.7，符合预期。

更简洁的单公式方案（适用于Excel 365/2021及以上）

通过数组运算直接匹配最小差值对应的结果，公式更短：
=INDEX(sheet2!$L$3:$L$534,MATCH(MIN(ABS(sheet2!$K$3:$K$534-G4)),ABS(sheet2!$K$3:$K$534-G4),0))

特殊场景调整

如果你需要排除sheet2中0值的干扰（如示例中的无效0数据），可以使用FILTER函数过滤后再匹配：
=INDEX(FILTER(sheet2!$L$3:$L$534,sheet2!$K$3:$K$534<>0),MATCH(MIN(ABS(FILTER(sheet2!$K$3:$K$534,sheet2!$K$3:$K$534<>0)-G4)),ABS(FILTER(sheet2!$K$3:$K$534,sheet2!$K$3:$K$534<>0)-G4),0))