Although this is fairly easy to do, I ran into an issue and wanted to share some tips.
Let’s say you want to return the matching text based on an existing list. In my case, I wanted to return the matching Country name based on a full address:
|5555 Almere, Netherlands||Netherlands|
|26, 34485 Sarıyer, Turkey||Turkey|
In a separate sheet “countries”, i have a full list of countries like:
So normally the formula should be:
=REGEXEXTRACT(A2,TEXTJOIN("|", 1, countries!A:A))
This didn’t work and I was stumped. I found the problem after various steps of troubleshooting!
Make sure your list is free of special characters like ()=-? etc. For example, check your country list. These are examples that would break your formula:
Congo (republic of congo) or
Congo [See Republic of Congo]
In my case I had brackets, parenthesis, and other chars that broke the regular expression search.