# Is there a formula to show if one column contains a duplicate number in another column and lenght of numbers vary?

Column A contains this: 007 0000486007 014400021031021983 4 0 Column B contains this: 486007

Is there a formula that could find the duplicate number in both columns although the number of digits vary in column A and column B?

-
What do you want the formula to return? TRUE/FALSE? The row number of the match? Or the actual matching (long) number? – Excellll May 11 '12 at 12:28

Assuming all characters are [0-9] and we just have leading 0 issues (only examples you gave led me to this conclusion)

in D1 `=value(A1)` (and fill down, do so with all of these)

in E1 `=value(B1)`

Now we have all leading zeros stripped and they are actual numbers.

in F1 `=vlookup(E1,\$D\$1:\$D\$10,1,false)` (when fillign down be careful to lock ranges as I have here) where \$D\$10 is whatever the last cell with a value in column D is

if you want a TRUE/FALSE value we can use some trickery

``````=IFERROR(OR(TRUE,VLOOKUP(E1,\$D\$1:\$D\$10,1,FALSE)), FALSE)
``````

The Or part gives us actual "TRUE" values, if vlookup is an error, or will be an error, so iferror will kick out to FALSE.

alternatly

``````=if(iserror(vlookup(E1,\$D\$1:\$D\$10,1,false)), FALSE, TRUE)
``````

Note if you were to lookup 0, 0 is a "FALSE" in excel, so we cant just use vlookup as test, also error will kick out there as well.

-