适配Google Sheets:解决FILTER/XLOOKUP无匹配报错,实现排名返回

报错核心原因 #

两款软件对XLOOKUP返回数组的处理逻辑存在底层差异：

• Excel端运行原公式时，当XLOOKUP遍历O2:AZ2的单元格、找不到匹配文本时返回的空字符串，会被自动从数组中剔除，仅保留有效数字组成的优先级序列传给后续SMALL函数
• Google Sheets端运行相同逻辑时，会把空字符串作为数组的有效元素保留，直接传给SMALL函数时会因为混入非数字值触发计算异常，最终连带FILTER抛出“未找到匹配项”的报错

可直接运行的适配公式 #

=LET(
  行内文本范围, O2:AZ2,
  逐格映射优先级, XLOOKUP(行内文本范围, $B$2:$B$20, $A$2:$A$20, ""),
  过滤空值后的有效优先级, FILTER(逐格映射优先级, 逐格映射优先级<>""),
  对应顺位的目标优先级值, IFERROR(SMALL(有效优先级, L2), ""),
  IFERROR(FILTER($B$2:$B$20, $A$2:$A$20=目标优先级值), "")
)

功能匹配说明 #

• 公式完全对齐原业务逻辑：每一行独立计算自身单元格内存储的文本子集对应的相对排名，不会跨行引用数据
• 自动处理边界场景：当输入的顺位数值超过当前行实际存在的文本数量时，直接返回空单元格，不会弹出报错
• 匹配规则校验示例：
  • 第2行存储的文本子集为Text1、Text3、Text5时，输入顺位1返回Text1、顺位2返回Text3、顺位3返回Text5，输入4及以上顺位值时单元格留空
  • 第3行存储的文本子集为Text2、Text3、Text4时，自动按全局优先级排序为Text2（顺位1）、Text3（顺位2）、Text4（顺位3），对应返回结果
• 注意事项：公式引用的全局优先级范围需保持绝对引用（即带$符号），避免公式下拉时自动偏移范围导致计算错误

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