How to use SEARCH for word not embedded in another word?

I am using the SEARCH function on a column of keywords, trying to match each cell to a list of words, and counting up how many keywords in that column have any of the words in a list. It all works fine except for one thing. If the list has a word that is short, sometimes a keyword that contains that short word within it gets counted.
For example, let's say my list of words I care about are:

• longword
• dia
• others

Then my list of keywords I want to match to my word list is:

• This is a longword
• dials
• dia
• concordia
• dia alone
• just dia
• others
• blah

So I only want to count the cells that have "dia" as its own word (in bold), not "dials" or "concordia." If this were a regular expression match, it would be easy. But how do I specify to Excel in a Search function that I only want these keywords when they match what's in the list only when it's got a space or beginning/end of line on either side?

BTW, I am using the function =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(listofwords,$A7))))>0,"found","") to do the matching, then have a COUNTIF at the bottom of the column to add up the cells with "found" in them.

Thanks!

Try

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&listofwords&" "," "&$A7&" "))))>0,"found","")

Don't think that will work. "listofnames" in the SEARCH function is a named array, not a string.

Only 1 way to find out.

Jonmo, initially I was thinking the same thing but I could not get it to work when testing.

It only seems to work for exact matches.

See attachment.

Works for me, something is wrong with the spaces in the book you posted
Retype the sentences in A1 A5 and A6 and it works.

Interesting, I copied and pasted the data from post #1.

After changing the characters that look like spaces to actual spaces, the formula did indeed work.

Awesome! It worked! I didn't know you could put parameters on either side of a named array. Many thanks!

You're welcome.