# Excel Function / Formula to evaluate &quot;field contains&quot; 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

Last Comment
barry houdini
Patrick Matthews

jspurr01 said:
>>1)  Worksheet1 contains 100,000 rows
[...]
>>Note:  I would prefer a solution that works in Excel 2003, but 2007 would be OK, if necessary

Well, if one of your worksheets has 100k rows, then you can't use Excel 2003 :)
Patrick Matthews

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
barry houdini

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Barry,

Quite true, my formula will match on partials, which may or may not be what is called for.

Man, I was feeling pretty impressed with myself about that formula, too!  :)

Regards,

Patrick
jspurr01

Greetings guys ... Thanks for the responses.

I will check these out and award points accordingly.

(for some reason, I am not getting email notifications any more when folks post)
jspurr01

Wow, both are really cool.

Actually, 'whole word' was not the objective, but I do occasionally have the need, so, Thanks also to BarryHoudini.

Amazingly, I actually understand these solutions ... except for the "Array Formula" concept.  I mean, I get the general idea, but can you point me to a faq or brief tutorial that gives some technical specifics of Array Formulae?

jspurr01

These 2 answers are the best I have ever received on the Experts Exchange.  Many Thanks to Both!!!
Chip Pearson describes array formulas here
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY