Check if a string of text contains a word from a list, and specify which word that it is.

[albanhac]

Hello,

CONTEXT:

I have a list of product IDs in column A and their titles in column B in a tab called "Products" (around 900,000). In the other tab called "Taxonomy" I have a list of category names in column B and their category ID in column A (around 10,000).

I would like create a formula which searches the product titles to see if they contain words that match the category names, and if so return which category ID it matches to.

I've found a way to confirm whether or not the title contains a category name in with this array formula:

{=IF(MAX(IFERROR(SEARCH(Taxonomy!B:B,Products!B2),0))>0,"yes","no")}

and I know how to return a category ID using the name with an INDEX MATCH formula, but I can't figure out or find any post that covers how to find out which word from the category list the formula has found in the title.

Does anyone know how I can do this or is this something only VBA can do?

Thank you in advance for any help

[Jacc]

I'm not sure I understood what you want but here are some suggestions.

[albanhac]

Thank you Jacc! This worked perfectly :D

[Jacc]

''''''''''