Excel Function / Formula to evaluate "field contains" from a list
I need a function that looks for substrings in a cell from a list of substrings in a adjacent worksheet.
Here is a scenario:
In A Workbook:
1) Worksheet1 contains 100,000 rows
2) Column C in Worksheet1 contains text phrases, 0 to 100 characters (maybe 0 to 20 words) and may have embedded punctuation.
3) Worksheet2 contains 20 rows
4) Column A in Worksheet2 contains 1 word in every row
5) **** I would like fields in Column D in Worksheet1 to contain TRUE if any word in Worksheet2 is contained in, or equal to the adjacent field C in Worksheet1, and FALSE, if not.
Obviously, if "Equal To" was the criteria, this is easily done using vlookup. What I need is "Equal to" OR "Contains".
Note: I would prefer a solution that works in Excel 2003, but 2007 would be OK, if necessary
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel