Finding exact word in an array of words

Hi all,

I'm new here but hope to become a contributing member over time! I also hope I am not out of line with posting my question to a problem I have encountered.

Goal: to determine if a word matches exactly in an array of words.

Description: I have a list of words that I am trying to see if they are present in particular cells

List of words (contained in a separate work sheet called Keyword Types:
who
what
when
where
why
how
can
is
?

Cell phrases:
how do I calculate the chi square test?
when will I get my marks back?
regression line
understanding the p-value
thesis of mathematical significance


Here is the function I've come up with:

{=IF(SUM(NOT(ISERROR(FIND('Keyword Types'!$A$2:$A$2,$A2)))*1)>0,G$1,"Non-"&LOWER(G$1))}

Now, my code does work as expected, too well is the problem because when I copy the code for each cell it will match the word "is" to the cell phrase "thesis of mathematical significance" since the phrase contains the word "is" but that's not what I am trying to do obviously.

Does anyone have some insight on how I may be able to revamp my function to match whole words only?

Thank you!!

Regards

Suggest that you actually search for the word with a space on either side or a space at the front and comma/full top at the end.

Originally Posted by DonkeyOte

Formula:

B2:

=IF(COUNT(INDEX(SEARCH(" "&'Keyword Types'!$A$1:$A$9&" "," "&$A2&" "),0)),G$1,"Non-"&LOWER(G$1))

copied down

Assumes keywords are in A1:A9 and A2 holds first phrase - and that case sensitivity is not an issue (apple and Apple would be a match).

First off, thanks to ALL for your generous help!!!

Secondly, this works amazingly well! You are a God!

One small question/hiccup. It does not seem to like searching for question marks (?). Do I need to escape the character some how so that excel recognizes it as a character/word to search on?

Thanks again everyone!

Originally Posted by DonkeyOte

Yes, sorry, ? is a wildcard (single character) so you should precede with tilde in your keyword list, i.e. replace ? with ~?

edit: may have an issue given that is not a single word (ie encasing within space)

hmm you're right.

DonkeyOte a God? does not get captured whereas DonkeyOte a God ? does get captured it seems.

weird!

I think this may be good enough anyways...

Originally Posted by DonkeyOte

Apologies but I am heading offline for a little while... if the ? is the exception (i.e only non word) then you could modify for that specific instance - alternatively if you intend to have multiple punctutation type keywords and no 1 length words (a, i) then you could modify based on length - e,g

Formula:

COUNT(INDEX(SEARCH(LOOKUP(LEN('Keyword Types'!$A$1:$A$9),{1,2},{"~"," "})&'Keyword Types'!$A$1:$A$9&LOOKUP(LEN('Keyword Types'!$A$1:$A$9),{1,2},{"~"," "})," "&$A1&" "),0))

Thanks again for your help. It took some time for me to figure out how to work with what you wrote but I eventually got it humming nicely!

Here's what the final code looks like:

=IF(COUNT(INDEX(SEARCH(LOOKUP(LEN('Keyword Types'!$A$2:$A$14),{1,2},{"~"," "})&'Keyword Types'!$A$2:$A$14&LOOKUP(LEN('Keyword Types'!$A$2:$A$14),{1,2},{"~"," "})," "&$A22&" "),0)),I$1,"Non-"&LOWER(I$1))

I really appreciate your efforts. You saved me SEVERAL hours of sorting through data and categorizing crap lol