find first word within text string that matches from a list of keywords

**exclusivebiz** · 04-13-2012, 02:16 PM

Hi,

I reviewed this post http://www.excelforum.com/excel-gene...-keywords.html
My request is slightly different.
I have a text string that sometimes has multiple names in it.
In the keyword list I would have all the names.
I would like to search the text string and return the first name it finds that matches any name in the keyword list.
It seems the above link does the opposite, it displays the names according to the order from the keyword list.

I played around with the formula but couldnt get it to do what i want.
Appreciate your assistance.

**Haseeb Avarakkan** · 04-13-2012, 02:39 PM

May be this, with CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

A sample file would be helpful with your desired results.

**exclusivebiz** · 04-13-2012, 02:56 PM

Hi,

Thank you for your prompt reply.
I have attached a sample file.
Would you be able to elaborate on your code as thus far its not working for me.

Once again, thank you.find first word.xlsx

**Ron Coderre** · 04-13-2012, 03:18 PM

Using your posted workbook...
This regular formula returns the first word in A2 that matches a word in the KEYWORDS list
C2: =INDEX(Keywords,MATCH(MIN(INDEX(SEARCH(Keywords,A2&Keywords),0)),INDEX(SEARCH(Keywords,A2&Keywords),0),0))

EDITED TO INCLUDE THIS COMMENT:
The above formula assumes that at least one of the keywords will be found on the list.
If that's not the case...
Put the words "No Match" at the top of the KEYWORDS list and that will display when that situation exists

Is that something you can work with?

**Haseeb Avarakkan** · 04-13-2012, 03:21 PM

Hi,

Based on your attachment assume you are on Excel 2007 or later. If so try this with CTRL+SHIFT+ENTER.

Please Login or Register  to view this content.

Of course experts will have more elegant ways.

**exclusivebiz** · 04-13-2012, 03:29 PM

Hi,

The reply that Ron gave worked for me. That is what I needed.
I wasn't able to get Haseebs code working for some reason. The CTRL+Shift+Enter didn't help.

Either way, I appreciate the responses and I'm good to go now.

Thank you.