Excel - Large List with keywords. Search in a cell to see if contains keyword and if yes return keyword that is found.

[witedwarf]

I have a range of Keywords (named range Devices) its a somewhat large list 586 lines (it will grow over time).

I have on another worksheet a large amount of data will grow to about 100,000+ lines by end of year.

I want to search to see if cell A2 in this worksheet contains one of the keywords in my "Devices" list and if it does contain the item in this list then return the vaule of the list.

Can someone help the closest I have came is a true/false or Yes/No result with an Array as in below


{=IF(OR(COUNTIF(A2,"*"&DEVICES&"*")), "Yes", "no")}

This returned yes as my list was found in A2 but I want what it actually found in my list

Thank you all for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

Some clarifying questions:
1. Is it possible that 2 or more of the keywords appear in a single cell? If so, what should the result be? (examples?)
2. If one of the keywords is "king" and the text in the cell being checked is "The kingdom of Nepal", is that a match? (part word)
3. If one of the keywords is "king" and the text in the cell being checked is "The King is dead", is that a match? (case match would fail but text match would pass)
4. If the answer to 2 is "no", only looking for whole words, then does (or could) the cell being checked contain punctuation like this "The king's clothes are expensive" or "Where is the king?"? Punctuation can make checking for whole words tricky.

 

[witedwarf]

1. it is possible but would be a limited result, the result could be something of the sort as "Multiple matches" and I would have to review these results.
2. yes that is a match for example I have "server123" in my "Devices" list and if server123.mydomain.com in the cell I am searching I want "server123" from my displayed.
3. Yes that is a match, as the casing is irrelevant using example I have "server123" in my "Devices" list and in the cells I am searching "Server123" I want "server123" from my list displayed.
4. I do not have any punctuation in my "Devices" list to search for and it is not required.

Thank you for your help with this! I really appreciate all assistance with this.

 

[Peter_SSs]

2. yes that is a match for example I have "server123" in my "Devices" list and if server123.mydomain.com in the cell I am searching I want "server123" from my displayed.

Does that one present a problem? Could you have "server 12" and "server123" in your devices list? If so, both of those would match in "server123.mydomain.com". Is that possible with your data?

And so if "server123" was NOT in your devices list but "server12" was and the cell to be checked has "server123.mydomain.com" then your answer to Q2 says that is a match. Is that correct?

I do not have any punctuation in my "Devices" list to search for and it is not required.

But you do have punctuation in the cell(s) to be checked (server123.mydomain.com) which is what I had asked about.


Do you care if the suggestion is a formula or uses vba?

 

[witedwarf]

Does that one present a problem? Could you have "server 12" and "server123" in your devices list? If so, both of those would match in "server123.mydomain.com". Is that possible with your data?

And so if "server123" was NOT in your devices list but "server12" was and the cell to be checked has "server123.mydomain.com" then your answer to Q2 says that is a match. Is that correct?

Yeah based of the logic it would but I do not want that to occur (if possible) so I want server12 only to locate server12. So for example I have l00123 and L00123AP I want l00123ap to be returned and not l00123 as there is a big difference. I am going to try and clean my list to be more exact and not too general but there is a distinct possibility of this to occur.

But you do have punctuation in the cell(s) to be checked (server123.mydomain.com) which is what I had asked about.


Do you care if the suggestion is a formula or uses vba?

Ah yes I do have punctuation. Sorry about that.

I am not very versed with VBA but I can attempt to do it. As I am assuming this is not going to be possible with a formula based off of some of the complexity I keep adding.

 

[Pete2020]

Welcome to the MrExcel board!

Some clarifying questions:
1. Is it possible that 2 or more of the keywords appear in a single cell? If so, what should the result be? (examples?)
2. If one of the keywords is "king" and the text in the cell being checked is "The kingdom of Nepal", is that a match? (part word)
3. If one of the keywords is "king" and the text in the cell being checked is "The King is dead", is that a match? (case match would fail but text match would pass)
4. If the answer to 2 is "no", only looking for whole words, then does (or could) the cell being checked contain punctuation like this "The king's clothes are expensive" or "Where is the king?"? Punctuation can make checking for whole words tricky.

Nice Questions...

Almost the same requirement with same amount of data i am looking for a VBA solution. Match and find Keywords. I remember in a thread you handled well in identifying singular and plurals while matching.Since 2 days i am trying to find that thread with your name but no luck.Finally i landed here.The Tags assigned to a thread are user at discretion.It is sometimes not helping people like us to find perfect solution

.@Peter_SSs, I updated in our Mr Excel Board a week ago,I am searching since 3 days for proper solution in the forum.

I did not have any luck. Here is the Link of my thread. Sorry to trouble You again

Advanced Lookup with Keyword Grouping