Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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


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.


=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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.