XLOOKUP的隐藏参数 巧妙计算销售阶梯提成

Excel中查询区间对应的数据是很常见的场景.

计算销售人员的提成：

销售额1~2000提成1%；销售额2001~5000提成2%；销售额5001~10000提成3%;10001以上提成5%.

小明的销售额3690，提成怎么算？

计算快递费：

300公里内8元；800公里内10元；1500公里内12元。

小明的快递跨越2876公里，快递费怎么算？

Excel中IF和IFS函数是常见的解决方案，区间段较多的时候公式会很长，容易出错。XLOOKUP是很好的解决方案，它有4种设置来指定不同的匹配模式：

【0】精确匹配

【-1】精确匹配或下一个较小的项

【1】精确匹配或下一个较小的项

【2】通配符匹配

第五参数是可选参数，不设置按【0】执行，用到通配符时需设置为【2】

【-1】和【1】两种设置都可以用于区间查询。

下一个较小的项

【-1】精确匹配或下一个较小的项

先执行精确匹配，如未能匹配成功，则匹配比查找值小的下一个值，例如在A列中查找6，未能精确匹配，则匹配比6小的下一个值4，返回它对应的C:

=XLOOKUP(6,A:A,B:B,,-1)

下一个较小的项

【1】精确匹配或下一个较大的项

先执行精确匹配，如未能匹配成功，则匹配比查找值大的下一个值，例如在A列中查找6，未能精确匹配，则匹配比6小的下一个值7，返回它对应的D:

=XLOOKUP(6,A:A,B:B,,1)

计算提成

以开头计算提成为例，在应用XLOOKUP前需将提成规则转换为对应关系作为辅助数据。

如红色字体部分，分别列出区间的上下限和提成比例的对应关系，将XLOOKUP第五参数设置为【-1】:

=XLOOKUP(B2,E:E,G:G,,-1)

以小明为例，XLOOKUP在E列中找不到2876，则匹配比它小的下一个项2000，返回2%.

也可以改用【1】:

=XLOOKUP(B2,F:F,G:G,,1)

与上一个方案对比有3点变化

XLOOKUP第二参数的查找范围变为F列；第五参数设置为【1】；辅助数据中黄色单元格输入一个足够大的数字，此处输入的是99999999999，显示为1E+11.

例如花花的17809，XLOOKUP不能精确匹配，则匹配比它大的下一个项1E+11，返回对应的5%.