How do I extract a string from a field that matches a text field in one of the records from another table?

Discussion created by johnfromberkeley on Jun 29, 2016
Latest reply on Jul 25, 2016 by user19752

I'm not sure if this is the right section in the forums.


I have a table of product titles with a single field containing text like this:


Sony headphones

Bose headphones

Panasonic headphones


I have a separate table that is a list of brands, like this:






How do identify the brand name in the product titles, by checking each product title to see if it matches one of the brand records? I'd like output like this:


(field 1)Sony headphones  (field 2)Sony

Bose headphones | Bose

Panasonic headphones | Panasonic


In other words, I have a list of product titles, and I have to classify each record against a list of brands.


I'm not looking for a data validation solution, like a picker. I'm looking to automate the task of populating the "brand" field of each record as it is imported.


This is not a one off job, otherwise, it would be trivial in Excel. I need this to function dynamically on an ongoing basis, there will be new product titles, and new brands added to the list over time.