VBA Code or Formula to Match Text Descriptions by # of Word Similarities

I am stumped on a recent request I have been given in excel and was hoping someone might be able to help me out. Broadly, we are trying to come up with a way to take two different lists of product descriptions and come up with up with matches based on the number of words that match in each description.

I made an example file (attached) of what the original two lists look like and what the intended outcome is supposed to look like. For this specific process, the team would like me to take the first 4 words of DESC LIST 1 and then pull in up to 5 descriptions from DESC LIST 2 where those 4 words can be found. For example, the first product would be split as follows (after comma removal):

1. Pretzel
2. Soft
3. Baked
4. Frozen

You will then see that there are 8 examples on the second list that contain those four words, and the first 5 of those examples are brought into the columns on the second tab. The trick is that the first 4 words of the DESC LIST 1 can be found in any order or location within the DESC LIST 2 product description – this is where I am struggling to come up with a formula or macro that can do this. Formulaically speaking, a match for this item could be something like “Baked Soft Everything Pretzel Bun, Frozen” just because it contains those 4 words within the text.

Thank you in advance for your time!

This is perfect, thank you! I do have two edits, if possible.

1. Could you move the results over one column so that the original DESC List 2 stays in place?
2. Is it possible to update the code so that if there are less than 4 words in the Desc List 1 (for example, "Pretzel, Soft Baked"), results would still pull for it using all of the words available?

If you could do specific word matches instead of partial word matches, that would be even better! thank you!

Hi,

If it is not easy to do the partial word match, it would still be very helpful to have a code that continued to work if some text strings are contain 3 or less words. Either way, let me know if this is possible! Thanks!

NicoleAL123