Excel“十字交叉”模糊匹配数据，请放弃IF函数

你好，我是小必，感谢与你在这里相遇。

今日内容：Excel中的交叉区间匹配

在日常的工作中，经常会遇到一类问题，是区间的匹配。如90%以上为100分，70-90为80分，70分以下为0.这类简单的区间匹配或者条件判断的问题可以使用IF来解决，再高级一点的可以使用LOOKUP或者VLOOKUP函数来实现。

但是今天需要说的三个关于区间匹配的例子，不同于上面的简单的例子，还是交叉的匹配多个交叉条件在一起的。下面我们一起来探讨这三种常见于绩效考核中的例子。

1十字交叉区间匹配

如图所示，某企业的一个考核的方案中有如下的一个考核规则：某岗位双向考核两个指标，一个是到课的比率；另外是根据带班的人数多少确定对应的绩效奖金基数。

根据以上的规则，计算下面的几个人的绩效奖金基数。

首先将上面的规则转换一下，做为一个辅助区域。即：

然后在I3单元格中输入以下公式，向下填充至I11单元格。

=INDEX($B$3:$E$7,MATCH($G3,$A$3:$A$7,1),MATCH($H3,$B$2:$E$2,1))

结果如图所示：

注意：上述公式主要使用INDEX+MATCH的组合策略，最重要的一个部分为MATCH的第三个参数是1，实现模糊匹配的作用。

2多列交叉区间匹配

如图所示，规则为：每个年级对应的上课率与对应的系数如下。

根据以上的规则，计算下面的年级与上课率对应的系数。

同样地先将上面的表格稍做转化。如下图所示。

然后在J2单元格中输入以下公式，向下填充至J6单元格。

=LOOKUP(I2,OFFSET(A$2:A$7,0,MATCH($H2,$A$1:$E$1,0)-1),$F$2:$F$7)

同样地，这个问题也可以使用第一个例子中所讲的INDEX+MATCH的套路组合来解决。在J2单元格中输入以下公式，向下填充至J6单元格。

=INDEX($F$2:$F$7,MATCH(I2,OFFSET(A$2:A$7,0,MATCH(H2,$A$1:$E$1,0)-1)))

结果如下图所示：

3多列多行十字交叉区间匹配

如图所示，规则为：每个年级对应的上课率与人数区间确定奖金基数。

根据以上的规则，计算下面的几个人的绩效奖金基数。

同样地先将上面的表格稍做转化。如下图所示。

然后在D12单元格中输入以下公式，向下填充至D20单元格。

=INDEX($D$4:$G$8,MATCH($B12,OFFSET(A$4:A$8,0,MATCH(A12,$A$3:$C$3,0)-1),1),MATCH($C12,$D$3:$G$3,1))

结果如图所示。

上面的三个例子是经常在日常工作中会出现的一些例子或规则，使用好公式，能够事半功倍。