最接近值查询，这种难题你肯定遇到过

在实际工作中经常遇到需要按最接近的匹配来查找值，如果直接使用VLOOKUP或者HLOOKUP的模糊匹配查询，将无法达到要求。

如图所示，A-F列是某个公司制造模具使用的一些钢材物料，每种钢材需按照长度、宽度、厚度之和，与右边H-L列中标准数据中匹配出相应的单价。

但查找长宽高之和的实际值，并不等同于标准的长宽高之和。

实际值取值时，如果实际值大于标准值区间中间值就取右端点（标准值）对应的价格，如果实际值小于该区间中间值则取左端点对应的值。

从以上内容可知，这实际上是一个按照按区间端点最近值查询的问题。

首先在K列定义名称：

“标准长宽高之和”

引用区域为：

=Sheet1!$K$2:$K$10

同样方法在L列定义名称 “标准价格”，引用区域为：

=Sheet1!$L$2:$L$10

方法1：

在F2单元格定义如下数组公式：

=ROUND(INDIRECT(ADDRESS(ROW(标准长宽高之和) MATCH(MIN(ABS(E2-标准长宽高之和)),ABS(E2-标准长宽高之和),0)-1,COLUMN(标准长宽高之和) 1))/1.17,2)

公式解析：

我们知道ADDRESS函数可返回一个单元格的地址，由该函数创建一个文本字符串并将其作为INDIRECT函数引用的文本字符串。

我们可以考虑创建一个查找值与各标准值（即长宽高之和）差异绝对值的数组，然后从该构造的数组区域中匹配出绝对值差异最小的位置，这样就确定了ADDRESS函数欲返回数据的行号。

至于ADDRESS函数的列号就可直接使用COLUMN(标准长宽高之和) 1确定即可。

方法2：

=ROUND(VLOOKUP(MIN(ABS($E2-标准长宽高之和)),IF({1,0},ABS($E2-标准长宽高之和),标准价格),2,0)/1.17,2)

公式解析：

利用实际值与各标准长宽高之和的之差的绝对值形成一个虚拟的内存数组即“ABS($E2-标准长宽高之和)”，并将这个差值绝对值的最小值作为VLOOKUP的查找值，在“ABS($E2-标准长宽高之和)”与“标准价格”形成的2列数据区域中查找标准价格。

这个其实是逆向查询的引申扩展用法。

方法3：

=ROUND(INDEX(标准价格,MATCH(SMALL(ABS(E2-标准长宽高之和),1),ABS(E2-标准长宽高之和),0))/1.17,2)

公式解析：

公式中通过E2单元格实际长宽高之和与每个标准长宽高之和的差值的绝对值形成一个内存数组。

在这个内存数组中通过SMALL(Array,1)确定出两者差值的绝对值最小值，通过MATCH函数定位出最小值在差值的绝对值形成一个内存数组中的位置，从而确定了所要引用的标准价格的行号。

提示：在上述三种方法中，如果区域中实际值与标准值之差的绝对值最小值有2个相同的数据，则此公式只返回区域中第一个出现的值。

 各位表亲，上述3种方法你认为哪个最简单？哪一个是你更喜欢的菜？

作者：ExcelHome学院助教 耿勇