Need to compare part of text in a range of cells to see if exists in another range of cells

Question

Basically... I have 800+ names in columns B and C. I have another 700,000+ names in column A. So I want to check A1 to see if part of the text is found anywhere in B and C and then output the answer to D1.

For example... A1 has the name mike trout in it. Somewhere in b or c could be the name trout. I want D1 to output TRUE because a cell in b or c contained part of the text found in A1. If the names Mike or Trout are nowhere to be found in columns b or c, I want D1 to say FALSE. Can this be done? I've been searching online for hours and cannot come up with the correct formula that works. Thanks.

Answer 1

This is a slightly more complex version of this answer: https://stackoverflow.com/a/22754109/293078. Both use COUNTIF with wildcards. This one requires a helper column. Here's how it looks:

Column B has a formula that adds a space before and after the corresponding name in column A. The formula in B2 is:

=" " & A2 & " "

This gives means that we'll only get matches for full words, e.g., "Ted" won't match "Teddy Bear," but "Teddy" will.

As you described, columns C and D contain the names to search for. Finally, in E2, we have this array formula, which must be entered with ctrl-shift-enter:

=SUM(COUNTIF(B2,"* " & $C$2:$D$8 &" *"))>0

Drag the formulas in B2 and E2 down 700,000 rows and watch the magic happen.

I thought this would be hopelessly slow, but I just tried 700,000 rows and it finished in a few seconds.