Excel INDEX+MATCH多条件查询：混合精确与近似匹配实现方案

需求说明 #

需要实现三条件匹配查询，规则如下：

• 第1、2列条件要求精确匹配
• 第3列条件要求使用「精确匹配或下一个较大项」的近似匹配规则
  匹配示例：

匹配条件：A、Y、185
期望返回结果：55.88
示例数据表格：

方案1：INDEX+MATCH组合实现 #

前提假设 #

• 原始数据存储区域为A2:D10，对应列规则：A列=第一条件列、B列=第二条件列、C列=第三数值条件列、D列=待返回结果列
• 查询条件分别存储在F1（第一条件值）、F2（第二条件值）、F3（第三条件值）
• 相同第一、第二列条件下的第三列数值按升序排列（近似匹配返回最小上界的必要前提）

公式代码 #

旧版Excel输入后需要按Ctrl+Shift+Enter触发数组计算，Excel 365/2021及以上版本直接回车即可：

=INDEX(D2:D10,MATCH(1,(A2:A10=F1)*(B2:B10=F2)*(C2:C10>=F3),0))

逻辑说明 #

• 用乘法规则同时校验前两列的精确匹配条件、第三列的大于等于查询值条件，满足所有条件的行返回逻辑值1
• 用MATCH精确匹配第一个返回1的位置，对应就是符合「前两列匹配、第三列是大于等于查询值的最小项」的行位置
• 最后用INDEX从结果列中取出对应位置的返回值

其他实现方案 #

方案2：XLOOKUP实现（仅支持Excel 365/2021及以上版本） #

公式更简洁，无需手动触发数组计算：

=XLOOKUP(F3,FILTER(C:C,(A:A=F1)*(B:B=F2)),FILTER(D:D,(A:A=F1)*(B:B=F2)),,-1)

直接调用XLOOKUP的第五参数-1，即可原生实现「精确匹配或下一个较大项」的匹配规则。

方案3：SUMPRODUCT实现（仅适用返回值为数值的场景） #

旧版Excel同样需要三键回车触发数组计算：

=SUMPRODUCT(MIN(IF((A2:A10=F1)*(B2:B10=F2)*(C2:C10>=F3),D2:D10,9^9)))

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