excel 表格中用VLOOKUP比对身份证号，两个身份证后四位不同，却显示相同

Question

两个身份证号只有后四位不同，但用VLOOKUP比对时候，表格认为两个身份证是一样的

Answer 1

注意：身份证号超出了15位！

超出15位的数字，即使你设置成文本格式，但在做某些计算或判断时，仍会出错。

在做某些计算或判断时，位数肯定是对的，但是第15位后面的位数，全部视为0，

举例：

A1是文本格式的123456789012345678

A2是文本格式的123456789012345123

现在用=COUNTIF(A1:A2,"123456789012345678")统计一下A1:A2区域中有几个123456789012345678，统计结果为有2个。

因为：

123456789012345678会被视为123456789012345000

123456789012345123也会被视为123456789012345000

像你用VLOOKUP时，会出同样的道理，导致的错误。

---------------------------------------------------------

【答案跟进更新】：

J2公式为：

=SUMPRODUCT((LEFT($C$2:$C$6,6)=LEFT($I2,6))*(RIGHT($C$2:$C$6,12)=RIGHT($I2,12))*$D$2:$D$6)

然后选中J2，下拉填充公式。

K2公式为：

=SUMPRODUCT((LEFT($C$2:$C$6,6)=LEFT($I2,6))*(RIGHT($C$2:$C$6,12)=RIGHT($I2,12))*($E$2:$E$6=--LEFT(K$1,FIND("月",K$1)-1))*$D$2:$D$6)

然后选中K2，右拉+下拉，填充公式。

提示：

在此，将身份证号拆成两段不足15位的数字，作为两个条件进行判断，这样就避免开了超出15位变成0的问题。

追问

请问那要怎么办呢

追答

分段判断
你具体要做什么，截图上来说明你要做什么运算。

追问

我就想求和工资，用SUMIF求和判断为三个身份证号一样

追答

答案已在上面作了更新。

Answer 2

把VLOOKUP函数最后一个参数换成0，你的应该是1追问

我的就是0，在高亮重复项中，两个位数不一样的身份证也会被列为一样的

追答

上图看看

Answer 3

比对提取A表格中，在B表格中也存在的身份证号有几个，需要加一个辅助列，用countif函数。
对比18位身份证号直接用=COUNTIF(A表格!$A$1:$A$9999,B表格!A1)会造成后三位不比对，
在条件哪儿加一个&“*”就解决了。
公式就是=COUNTIF(A表格!$A$1:$A$9999,B表格!A1&"*")

Answer 4

假如数据在A列，选择整个A列
在条件格式中输入：
=COUNTIF(A;A,A1)>1 样式为填充红色或其它；
如果是B列，公式里面A换成B即可。

Answer 5

身份证号虽然设为文本格式，但公式会将其当数值处理，受运算精度影响（只能精确运算15位数字），超出位数会被忽略，造成引用出错。