Excel VLOOKUP近似匹配返回错误前一行数据问题求助

原有写法失效原因 #

• 第一种写法存在语法错误：Excel中判断是否为缺值的函数是ISNA()而非你写的isnv()，同时括号未正确配对，因此运行直接报错无效果
• 第二种写法逻辑不符合需求：VLOOKUP近似匹配（最后一个参数为TRUE）的默认规则是返回小于等于查询值的最大匹配项，因此只要未查询到完全匹配的值，就会自动返回更小的上一行结果，自然和你预期的结果偏差较大

正确实现方案 #

前提要求 #

所有近似匹配类函数都要求查询矩阵的第一列提前完成升序排序，否则所有匹配结果都会出现逻辑错误。

方案1：查找最接近查询值的结果（适配Excel 2021/365及以上版本） #

使用XLOOKUP组合绝对值计算，直接匹配和查询值差值最小的项：

=XLOOKUP(MIN(ABS(my_matrix[第一列]-my_search_value)), ABS(my_matrix[第一列]-my_search_value), my_matrix[第二列])

方案2：未匹配到精确值时返回更大的相邻结果（适配Excel 2021/365及以上版本） #

直接调用XLOOKUP的匹配模式参数即可实现：

=XLOOKUP(my_search_value, my_matrix[第一列], my_matrix[第二列], "", 1)

方案3：低版本Excel兼容方案 #

使用INDEX+MATCH组合实现上界匹配：

=INDEX(my_matrix[第二列], MATCH(my_search_value, my_matrix[第一列], 1)+1)

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