VLOOKUP函数搭配OFFSET和MATCH函数近似匹配，经典函数嵌套案例！

这是个非常经典的函数嵌套案例,用到几率很高,关于这个问题,我也被提问好多次了!

(可以直接观看视频解析,下载课件练习一次)

看看需求,试一试你的思路..

下方这个表格是一份KPI和完成率的表格:

下方表格是考核规则:

先看上面表格人员的KPI得分,满足KPI得分的情况下找到对应的完成率区间,最后给到对应的兑现系数.

这是个很常见的例子,大家如果看过之前讲过的近似匹配案例,你当然就会考虑到从近似匹配方面进行切入解决这个问题.

问题难点:考核体系内容太多.

如果用IF函数,估计写到明天也写不完.

类似于这种问题,大家需要特别注意的点在于,表格的整理,上面这个考核规则的表格方便直接进行阅读,但是在使用函数的时候,却无从下手,所以一定要根据规则来整理一个可以套用的近似匹配查找表格:

这一份表格很长,需要注意的是,把KPI归类之后,做整体的升序排列,

在每一个KPI归类中,完成率同样需要做升序排列.

只有整理好这个表格,你才能够做出最后的结果,在整个过程中这个表格的整理也是属于最核心的部分.

=VLOOKUP(C2,OFFSET($F$1,MATCH(B2,$F$2:$F$49),1,-6,2),2)

稍微解释一下此公式:

例如:姓名A

MATCH(B2,$F$2:$F$49)

MATCH员工A的KPI,做近似匹配,所以输出结果为:12

MATCH函数会找到比员工A的KPI:45接近但是比45小的最后一个结果所在的位置.

OFFSET($F$1,MATCH(B2,$F$2:$F$49),1,-6,2)

由于每一项KPI区域对应到的都是6行的完成率以及兑现系数的区域,

所以OFFSET以F1单元格为基点,移动到40区间的最后一个单元格也就是第13行,然后右侧移动一列,高度倒退6行,两列宽度.

换而言之,这个OFFSET函数得到的区域,就是下方标记的位置:

然后在满足为当前KPI考核的情况下,在后面的完成率区域进行近似匹配:

VLOOKUP(C2,OFFSET($F$1,MATCH(B2,$F$2:$F$49),1,-6,2),2)

特别说明:

即使保存为永久链接,也会存在失效的可能,

如果某一天下载链接失效,请大家不要产生误解.

更新不易,

如果喜欢老徐的内容记得给老徐点赞哦!!

谢谢大家的观看!!

本章视频: