Find instances of substrings in a List of strings

[tony.rugbyfan]

I have a master list of top keywords, and I want to search for variations of them in the remaining list of keywords.

Top Keyword List ------------ Remaining List
tall trees------------------------- fast blue cars
blue cars------------------------blue cars online
--------------------------------------trees grow tall
--------------------------------------grow tall trees
--------------------------------------blue cars rock
--------------------------------------really blue cars
--------------------------------------green tall trees

What I need is a formula that I can copy down next to the Remaining List, which checks if each of the values contains any of the substrings in the Top Keyword List, it can give a value of 1 or the substring etc

Maybe VBA is needed? Any help much appreciated..

 

[Andrew Poulsom]

Try eg in B2 copied down:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A$2:A$3,B2))))>0

which will return TRUE if the cell contains one of the phrases in A2:A3.

 

[schielrn]

Can you give some expected results?

Would grow tall trees be a match with keyword tall trees? As they are not in the same order?

 

[tony.rugbyfan]

Try eg in B2 copied down:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A$2:A$3,B2))))>0

which will return TRUE if the cell contains one of the phrases in A2:A3.

This seems to be working as I needed, many thanks....

I just now need to figure out how it is working!

 

[tony.rugbyfan]

Can you give some expected results?

Would grow tall trees be a match with keyword tall trees? As they are not in the same order?

hi schielrn,

the "trees grow tall" cell does not contain the phrase in the same order so I would be expecting false to be returned.

However, if it were possible to match the phrase even if it was not in the same order, then that would definitely eliminate a lot of work too.....