如何使用VLOOKUP获取匹配或落在范围内的对应值？

一、精确匹配的替代方案（常规VLOOKUP失效时） #

如果因查找值不在数据源首列、需反向查找等导致常规VLOOKUP失效，且无法新增辅助列，可使用嵌套的INDEX+MATCH公式：

=INDEX(返回区域, MATCH(查找值, 查找区域, 0))

示例：从Sheet2的B列返回对应Sheet1 A列匹配的值：

=INDEX(Sheet2!$B$2:$B$1000, MATCH(Sheet1!$A2, Sheet2!$A$2:$A$1000, 0))

注：旧版Excel输入后需按Ctrl+Shift+Enter触发数组计算，新版Excel直接回车即可

二、范围匹配的实现（无需辅助列） #

若要匹配落在指定区间内的值（如数值在[最小值,最大值]区间时返回对应结果），可选用以下两种方法：

方法1：LOOKUP函数（适用于升序排列的区间） #

假设Sheet2中A列为区间下限、B列为区间上限、C列为对应返回值，Sheet1的A2为待判断数值：

=LOOKUP(2,1/(Sheet2!$A$2:$A$1000<=Sheet1!$A2)*(Sheet2!$B$2:$B$1000>=Sheet1!$A2),Sheet2!$C$2:$C$1000)

原理：通过(Sheet2!$A$2:$A$1000<=Sheet1!$A2)*(Sheet2!$B$2:$B$1000>=Sheet1!$A2)生成布尔数组，符合条件的返回1，其余为0；1/数组后符合条件的项为1，其余为错误值，LOOKUP会忽略错误值，定位到最后一个符合条件的项并返回对应C列值。

方法2：INDEX+MATCH数组公式（不限区间排序） #

=INDEX(Sheet2!$C$2:$C$1000, MATCH(1,(Sheet2!$A$2:$A$1000<=Sheet1!$A2)*(Sheet2!$B$2:$B$1000>=Sheet1!$A2),0))

旧版Excel需按Ctrl+Shift+Enter触发数组计算

三、大文件性能优化建议 #

针对体积大、关联多工作表的文件，需减少公式计算量避免卡顿：

• 替换整列引用（如$A:$A）为实际数据范围（如$A$2:$A$1000）
• 开启手动重算：点击「公式」选项卡→「计算选项」→选择「手动」，按需按F9刷新计算结果

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