Match partial duplicates in two columns

Ebippity

New Member
I'm trying to find partial dupes comparing data in two columns and limiting the matches to a specified number of digits.

Ridiculously Simple Example: If column 1 has "1234" and column 2 has "3456" (and these are formatted as text, not numbers), it would match those because they both "34" in them. I would be able to specify that at least 2 consecutive digits have to match.

Here is a sample of the data I'm working with. How do I do this?

 First Column Second Column Match Column VV GJ-900-0003492 BLAH MATCH BLAH GJ-900-0003493 BLAH BLAH MATCH MP-002-00214 MANUAL PAYMENTS NO MATCH MP-900-00101 MANUAL PAYMENTS NO MATCH VGJ-900-00083 VOID GJ-900-0003492 MATCH VGJ-900-00085 VOID GJ-900-0003493 MATCH GJ-003-0001374 write off year-old GPN MATCH GJ-003-0001517 write off year-old GPN NO MATCH VGJ-003-00089 VOID GJ-003-0001374 MATCH GJ-003-0001374 write off year-old GPN MATCH GJ-003-0001517 write off year-old GPN NO MATCH VGJ-003-00089 VOID GJ-003-0001374 MATCH

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you for any help on this!!

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ebippity

New Member
Oh except I made a mistake. The third row from the bottom of the second column is NOT a match.

Ebippity

New Member
Oh wait... it IS a match. Sheesh. Anyone know how to delete or edit your own posts?

Phuoc

Board Regular
=IF(COUNTIF(\$A\$2:\$A\$100,"*"&B2&"*")+SUMPRODUCt(COUNTIF(B2,"*"&\$A\$2:\$A\$100&"*")),"MATCH","")

Ebippity

New Member
=IF(COUNTIF(\$A\$2:\$A\$100,"*"&B2&"*")+SUMPRODUCt(COUNTIF(B2,"*"&\$A\$2:\$A\$100&"*")),"MATCH","")

Phuoc, sorry for taking so long to respond. It's been crazy over here! Finally got a chance to test this out and I'm so excited that it's working. Thank you!!!