HELP - Evaluate large string in a cell against smaller strings in a List

[caddidle]

I have spent some time trying many of the similar formulas and strategies that would appear to be what I'm trying to do but I've not been able to get a single one to work. I need to evaluate whether a record (row) contains one of the string phrases in a list (Sheet 1 c2:c14) against the Q cell in Sheet 2 which is a larger string. What I've got right now is:

=IF(ISNUMBER(LOOKUP(9.99+307,SEARCH(" "&Sheet1!$C$2:$C$640&" "," "&Sheet2!Q3&" "))),"Yes","""")

But this is not working as I've tried removing all the words in the reference list and substituting it with my name (which I know is not in there) and have gotten Yes's as results. The result can be a yes or no or a numerical value. I'm trying to filter out over 5000 records for the top 10 descriptive two word phrases and do a geographic heat map based on each record. I've spent a few days and should have probably started with just posting but now I'm down to the wire for my very last class in grad school and anyone that can help me out with this would be my HERO! sample ufo set.xlsx Please help!

Claude

[Jonmo1]

This will give a True/False

=ISNUMBER(LOOKUP(2^15,SEARCH('Sheet 1'!C2:C14,'Sheet 2'!Q2)))

Note, there cannot be any blanks in C2:C14 on Sheet 1.

[caddidle]

Jonmo1 You are a life saver! Now to work with the larger set. Could you by chance explain the 2^15 part of the lookup statement? I need to go take an advanced Excel course!

Thanks again!

[Jonmo1]

2^15 is 2 to the power of 15 = 32768
The longest string allowed in a cell is 32767 characters.
So using 2^15 ensures that the lookup # is larger than any possible result of the Search Function.


Hope that helps.