Not sure of the terminology but I think what I want to do is reverse the logic of this vlookup , amend the wildcard slightly so that only full word matches are found and I guess to do this it has to be converted to a VBA loop and the search text split into an array.
=IF(LEN(Products[Name] )>2, VLOOKUP("*" & Products[Name] &"*",Orders[Description],1,FALSE),"N/A")
The above works on a list of product names, finding those that appear in order descriptions. What I really need to do is loop over the order descriptions and add the first match of any product name into an adjacent column so that each order description has the product name next to it. Also the above gives a few false positives where the product name is contained in a word. So I need to only match where the product name is immediately preceded and followed by a space or other special character such as hyphen,forward slash, backslash or parenthesis.
Any help appreciated.