Compare 2 lists, look for substrings from List1 in larger strings in List2

[svendfj]

Hello! I have a Workbook that is updated every week with a list, in column A on Sheet1. I

want to go through the Sheet1 list one cell at a time, and look for matches in Sheet2, column A, and copy the text from that cell on Sheet2 into column B for the current row in Sheet1.

The Sheet1 strings are a subset of the Sheet2 strings. For example, I would take "red cat" from a cell in Sheet1 and be able to find a match in a Sheet2 cell that contains 'small red cat and brown dog". ​

​

<strike></strike>

My research has not revealed existing programs that can easily be leveraged.

Thank you in advance for any perspective you can please share. ​

Sven

 

[jtakw]

Awesome jtakw!

You're welcome.

The Concatenation idea Aladin suggested is a good idea; however, if 2 or 3 Columns are populated on the same row (say A1, B1, and C1), the end result will be a combined string of All 3, which may or may not be what the OP is after.

 

[svendfj]

Very good. Thank you Aladin, I appreciate it.