如何在输入重复数字时自动填充B、C列？公式失效求助

原公式失效的可能原因 #

• VLOOKUP 精确匹配模式下，若查找范围内存在多个重复值，只会返回第一个匹配项，如果该匹配项的B/C列为空，就会显示空值
• 即使统一了单元格显示格式，仍可能存在隐式数据类型差异（比如单元格存储的是数值型数字，手动输入的是文本型数字），导致COUNTIF和VLOOKUP匹配失败

替代解决方案 #

方案1：用XLOOKUP实现稳定匹配 #

适用于匹配任意位置的重复值，自动填充对应B/C列内容：

• B列公式：=IF(COUNTIF($A:$A,A6)>1,XLOOKUP(A6,$A:$A,$B:$B,"",0,1),"")
• C列公式：=IF(COUNTIF($A:$A,A6)>1,XLOOKUP(A6,$A:$A,$C:$C,"",0,1),"")
  解释：XLOOKUP的0参数表示精确匹配，1表示返回第一个匹配项；当A列当前值出现次数超过1次时，自动填充对应列内容

方案2：仅匹配当前行之前的历史数据 #

如果只希望填充已输入过的历史数据，避免匹配当前行自身：

• B列公式：=IF(COUNTIF(A$1:A5,A6)>0,XLOOKUP(A6,A$1:A5,B$1:B5,""),"")
• C列公式：=IF(COUNTIF(A$1:A5,A6)>0,XLOOKUP(A6,A$1:A5,C$1:C5,""),"")
  解释：A$1:A5限定查找范围为当前行上方的区域，确保只匹配已录入的历史值

方案3：统一数据类型解决匹配误差 #

若存在数据类型差异导致的匹配失败，可将所有A列内容统一转为文本格式后匹配：

• B列公式：=IF(COUNTIF(TEXT($A:$A,"0"),TEXT(A6,"0"))>1,XLOOKUP(TEXT(A6,"0"),TEXT($A:$A,"0"),$B:$B,""),"")
• C列公式：=IF(COUNTIF(TEXT($A:$A,"0"),TEXT(A6,"0"))>1,XLOOKUP(TEXT(A6,"0"),TEXT($A:$A,"0"),$C:$C,""),"")

批量操作提示 #

选中B列需要填充的单元格区域，输入对应公式后按Ctrl+Enter批量应用；若需整列自动填充，可使用Google Sheets数组公式：

• B列数组公式：=ARRAYFORMULA(IF(A:A="","",IF(COUNTIF(A:A,A:A)>1,XLOOKUP(A:A,A:A,B:B,"",0,1),"")))

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