XLOOKUP结合AutoFill匹配存在却返回#N/A问题问询

问题现象 #

• 使用XLOOKUP处理平均长度约400的长字符串，已确认lookup_array与return_array行数一致、位置对应，且填充时通过绝对引用锚定了固定范围（如C$3:C$11631、A$3:AB$11631）
• 通过VBA批量填充公式或手动下拉填充后，部分单元格返回#N/A，但实际存在匹配项

  .Range(.Cells(dataRow, nextCol), .Cells(nRows, nextCol)).Formula2 = XLOOKUP(C3,C$3:C$11631, A$3:AB$11631)
  

• 点击返回#N/A的单元格编辑栏并回车重新执行函数，即可得到正确结果

复现步骤 #

1. 创建包含20000行的表格，其中「Long String」列为仅末尾数字不同的长字符串
2. 在D1单元格输入公式：

   =XLOOKUP(B1,B$1:B$20000,A$1:C$20000)
   

3. 下拉填充至D20000，此时部分单元格会返回#N/A

临时解决方法 #

放弃批量填充/FillDown方式，改用For循环逐行插入XLOOKUP函数，强制每个单元格独立完成计算。

可能的原因分析 #

• 批量计算缓存延迟：处理大量长字符串时，Excel批量填充的计算机制可能依赖缓存快速完成，未能对所有单元格执行完整的字符串匹配校验，导致部分匹配被误判为无结果；手动回车触发单单元格计算时，会强制执行完整比对，因此得到正确结果。
• 长字符串哈希匹配冲突：XLOOKUP内部可能采用哈希值快速匹配字符串，批量处理长字符串时，哈希计算可能出现冲突，导致匹配失败；单单元格重新计算时会重新生成哈希或切换为精确字符串比对，规避了冲突问题。
• 自动填充的异步计算bug：在高行数、长字符串的场景下，Excel自动填充功能的同步计算逻辑存在漏洞，未能正确传递所有单元格的匹配上下文，导致部分单元格计算不完整。

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