如何让Excel XMATCH函数全场景返回数值并规避#N/A？

场景1：值列表为升序排列（如分界点从小到大：5,10,20,40,60,80,100） #

需求对应：

• 测试值 < 最小值（5）→ 返回7
• 测试值在区间内 → 返回对应位置（如5≤测试值<10返回6，…，80≤测试值<100返回2）
• 测试值 ≥ 最大值（100）→ 返回1

使用公式：

=8 - IFNA(XMATCH(测试值, 值列表区域, 1), 1)

嵌套进INDEX的写法：

=INDEX(返回结果区域, 8 - IFNA(XMATCH(测试值, 值列表区域, 1), 1))

原理：

• XMATCH(...,1) 用于升序列表中查找小于等于测试值的最大项，返回对应位置；当测试值小于最小值时返回#N/A
• IFNA(...,1) 将#N/A替换为1，结合前面的8-，正好得到7（对应小于最小值的场景）
• 对于测试值≥最大值的情况，XMATCH返回7，8-7=1 正好符合需求

场景2：值列表为降序排列（如分界点从大到小：100,80,60,40,20,10,5） #

需求对应：

• 测试值 > 最大值（100）→ 返回1
• 测试值在区间内 → 返回对应位置（如80<测试值≤100返回2，…，10<测试值≤20返回6）
• 测试值 ≤ 最小值（5）→ 返回7

使用公式：

=IFNA(XMATCH(测试值, 值列表区域, -1), 1)

嵌套进INDEX的写法：

=INDEX(返回结果区域, IFNA(XMATCH(测试值, 值列表区域, -1), 1))

原理：

• XMATCH(...,-1) 用于降序列表中查找大于等于测试值的最小项，返回对应位置；当测试值大于最大值时返回#N/A
• IFNA(...,1) 将#N/A替换为1，正好满足测试值大于最大值的场景
• 对于测试值≤最小值的情况，XMATCH直接返回7，无需额外处理

为什么不用复杂IF嵌套？ #

IFNA是专门处理#N/A错误的函数，逻辑单一清晰，嵌套进INDEX后整体公式结构简洁，可读性和可调试性都远胜于多层IF判断。

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