jspurr01
asked on
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
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
Last Comment
ASKER CERTIFIED SOLUTION
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
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
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
ASKER
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)
I will check these out and award points accordingly.
(for some reason, I am not getting email notifications any more when folks post)
ASKER
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?
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?
ASKER
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
>>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 :)