If string contains text from list, write matched text to cell

1
votes

I'm working on a large Google spreadsheet with key phrases, strings of text, in column A. I want to search column A based on a list of keywords that live in another sheet. When a keyword matches a word in a string in column A, I want to print that word in an adjacent cell to column A.

Here's a simple spreadsheet to work with that I think demonstrates what I'm trying to do.

https://docs.google.com/spreadsheets/d/1tNcroABVP0UdP4CiJldxLZgdrJF33TYT4mL1DZJfD1Q/edit?usp=sharing

2
regexgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin

2 Answers

2
votes

I want to print that word in an adjacent cell to column A

=ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A, LOWER(TEXTJOIN("|", 1, 'KEYWORD LIST'!A2:A)))))

0

0
votes

Try with this (put at B2):

=arrayformula(if(ISNUMBER(find(
        transpose({'KEYWORD LIST'!$A$2:$A$6}),A2:A6)), 
        transpose({'KEYWORD LIST'!$A$2:$A$6}),""))