XLOOKUP双条件搭配match_mode=1结果异常问题排查

你的公式出现异常主要有两个核心问题：

• 字符串拼接的排序逻辑不符合数值需求：
  你将样本名称和深度拼接成字符串（如Sample1&6得到Sample16），Excel会按文本排序规则处理这些拼接值，而非数值排序。比如：
  • Sample1&9得到Sample19，Sample1&10.85得到Sample110.85，文本排序中1的ASCII码小于9，所以Sample110.85会被判定为小于Sample19，导致XLOOKUP认为10.85的深度比9小，找不到符合条件的项；
  • Sample1&6得到Sample16，Sample1&60得到Sample160，文本排序中Sample16 < Sample160 < Sample17.75，如果查找区域的顺序不匹配文本排序，XLOOKUP就会跳过7.75直接匹配到60。
• 升序匹配的前提条件未满足：
  使用match_mode=1（查找大于等于查找值的最小项）时，XLOOKUP要求查找数组必须按升序排列。如果你的$D$3:$D$70&$E$3:$E$70拼接数组没有按升序排序，函数无法正确定位到目标值，返回错误结果。

针对你的需求（查找对应样本下一个更大的深度），推荐使用以下公式（适用于Excel 365及以上版本）：

=XLOOKUP(1, ($D$3:$D$70=A3)*($E$3:$E$70>B3), $E$3:$E$70, "not found", 1)

或者用FILTER+MIN组合，逻辑更直观：

=MIN(FILTER($E$3:$E$70, ($D$3:$D$70=A3)*($E$3:$E$70>=B3), "not found"))

比截图更高效的分享方式有这些：

• Markdown表格：直接整理成结构化表格，方便他人复制验证：

  样本名称	深度
  Sample1	6
  Sample1	7.75
  Sample1	9
  Sample1	10.85
  Sample1	60

• CSV文件：将数据导出为CSV格式，纯文本结构，任何表格软件都能直接打开，完整保留数据关系；
• 结构化列表：列出关键数据对，比如：
  • Sample1的深度集合：6, 7.75, 9, 10.85, 60
  • 需要匹配的测试项：Sample1+6（期望返回7.75）、Sample1+9（期望返回10.85）

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