VLOOKUP to find a substring exist in another column of list string

Hi, I am trying to look for this solution:

For example, I have two worksheets:
In sheet A, Column A contains a list of "Full Name", e.g. "Peter Parker JC", "Daniel Jackson"
In sheet B (namelist), Column A contains a list of "Family Name" like "Parker", "Jackson", etc.

And now I wan to find in Sheet A column A, each cell (A1,A2,A3, ...) contains any family name of column A of sheet B? (which is case-insensitive)

I want to show the result (TRUE/ FALSE) in Sheet A column B,

I tried to do something like this: =ISNUMBER(LOOKUP(9.99999E+307,SEARCH(" " & namelist!A:A & " "," " & B2 & " ")))
it works but the calculating speed is too slow, as I may have entries like >10000.

Is there any optimized solution to achieve this?

Really Thanks for your help!

Trye this

Formula:

Please Login or Register  to view this content.

If this is not working then attach a sample file

Originally Posted by AmirSoft

Trye this

Formula:

Please Login or Register  to view this content.

Thanks AmirSoft! Seems I know why the speed is so slow. It is because of the range I selected in sheet B column A is too large. After reference with your ans, I adjust it to a smaller range like 1000 rows, it is much faster now! (I guess x10 ~ x20 faster)

Thanks a lot!

You Welcome.

Now just mark this thread as solved from thread tools.

If you think that I really helped you then
<<<<<<<< Add reputation to this thread.