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

计算销售人员的提成：

销售额1~2000提成1%；

销售额2001~5000提成2%；

销售额5001~10000提成3%;

10001以上提成5%.

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

计算快递费：

300公里内8元；

800公里内10元；

1500公里内12元。

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

XLOOKUP第五参数

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，则匹配比它小的下一个项2001，返回2%.

也可以改用【1】:

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

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

XLOOKUP第二参数的查找范围变为F列；

第五参数设置为【1】；

辅助数据中黄色单元格输入一个足够大的数字，此处输入的是99999999999，显示为1E+11.

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