What is the best way to find matches between two columns.

Welcome to MrExcel - looks like you may be in need of Alan's FuzzyMatch code.

Sorry for adding more questions to this post, but I was just in the process of asking something similar. I haven’t used Excel that much, but what follows is similar to a “double join” in Lotus Approach. Is the FuzzyMatch code mentioned here all that is needed to address my problem? Any help is appreciated!

I have two sheets having varying numbers of records created from different sources. I want to compare sheet 1 to find matching records to sheet 2. Each record will have a product name and a product number column, and these are the fields to be used in the comparison. I want to determine which records in sheet 1 match (and do not match) any records in sheet 2. Given the rough example and the following information,

[HTML removed by Von Pookie]


• sheet 1 will undoubtedly contain fewer records than sheet 2 (possibly several hundred vs. 10,000 plus);
• The product name may not be unique to the product number, i.e. multiple names could exist for one product number (see red);
• The names may be written differently between the two sheets, yet they still should be a match if the number is the same (see all but black);
• Some product numbers may have additional digits on the end (see red).

Is there a way to match records between the two sheets with these two fields without having an exact match or specifying which sets of characters (1-3, 1-7, right 5, middle 3, etc) to use? That is, is there a way to set the matched substring length to say 5 or 7, and have a match returned if any 5 or 7 sequential characters within the string match?