VBA code to return product name from partial text match

Hi,

I am hoping someone can help me.

I have a list of products in sheet 1 of the attached and need to return the barcode for these products based off a list of products in a second sheet. However, the product names in sheet 1 do not fully match the product names in sheet 2. There are also multiple potential matches when using a partial text match.

If someone could please help me with a formula or better code that could return all the potential product name matches in sheet 1 from sheet 2, next to the products in sheet 1, I could then retain the correct product and then do a vlookup for the barcode.

I have attached a sample of the data CP 1 as well as some code that I found on this forum that may be able to assist although I don't know have to make this repeat to return all the possible product names from sheet 2.

Please help!

Many thanks in advance.