Excel MATCH函数数组匹配失败但指向精确单元格正常的原因及修复方法

• 使用ISNUMBER、MATCH组合函数校验目标工作表内是否存在指定值时，首次以数组作为MATCH查找范围，部分结果返回FALSE，手动核查源数据可确认对应目标值实际存在。
• 二次测试时将MATCH的查找范围直接指向目标值所在的精确单元格，函数可正常返回TRUE。
  

• 数据类型不匹配：数组范围内的目标值如果是带前置单引号的文本型数字、导入生成的带不可见隐藏字符（零宽空格、非打印换行、首尾空格）的文本，而查找值是纯数值格式时，MATCH的精确匹配模式不会做跨类型自动转换，就会匹配失败返回错误值，最终ISNUMBER判定为FALSE；直接引用目标单元格时Excel会自动做隐式类型转换，因此能匹配成功返回TRUE。
• 查找范围引用错误：跨表引用数组时未锁定绝对引用，公式填充时查找范围偏移漏过目标值所在区域，或是初始选范围时就没包含目标单元格，都会导致匹配失败。
• 旧版Excel数组逻辑未触发：2019及更早版本的Excel不支持动态数组自动溢出，输入数组类公式后如果直接按回车，MATCH只会读取查找范围的第一个单元格内容做匹配，无法遍历整个数组，自然会漏掉不在首行/首列的目标值。

• 统一数据格式+清洗脏数据
  匹配前先对两边内容做格式统一：文本型数字和数值不匹配时，可给查找值加--转为数值，或用TEXT(查找值,"@")转为文本；如果数据带不可见字符，用TRIM()清除首尾空格、CLEAN()清除非打印字符后再做匹配。带容错的匹配公式参考：

  =ISNUMBER(MATCH(TRIM(CLEAN(查找值)),TRIM(CLEAN(查找范围)),0))
  

  旧版Excel输入上述公式需按Ctrl+Shift+Enter三键确认数组运算
• 修正引用规则
  固定查找范围时要加绝对引用符号$，例如跨表引用A列1到1000行的固定范围写为Sheet2!$A$1:$A$1000，避免公式填充时范围偏移，同时确认所选范围完整覆盖所有待匹配数据。
• 替换为兼容性更强的写法
  如果不想处理数组三键、类型匹配的问题，可直接替换为COUNTIF做存在性校验，全版本Excel通用且不需要数组触发，写法如下：

  =COUNTIF(查找范围,查找值)>0
  

  该写法会自动做基础类型兼容，返回TRUE代表值存在，FALSE代表不存在，效果和原组合函数一致。

内容的提问来源于stack exchange，提问作者Carl Joshuel Gonzales