Google Sheets – Bulk Find & Replace Using List of Phrases

google sheetsgoogle-apps-scriptgoogle-sheets-arrayformulagoogle-sheets-arraysgoogle-sheets-query

This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated.

What I'm trying to accomplish
https://docs.google.com/spreadsheets/d/1p12KwPUr5UChGqxmoJz3UbuTOEyt2ai7PlPQxSOAodk/edit#gid=0
Example Data Screenshot

Cells in Column B are made up of multiple phrases separated by commas. Some of those phrases are listed in Column C. Column B needs to be searched using the phrases in Column C. If phrases are found they should be replaced with corresponding values from Column D (C1 to D1, C2 to D2, etc.) and listed in Column E, each value separated by ", ". Example:

  • Search B1 for all phrases listed in C:C
  • Say C1, C3, C6 phrases are found in B1 then values from D1, D3, D6 are listed in E1, each value separated by commas (found-value1, found-value2, found-value3).
  • This process repeats B2:B populating E2:E with values from D2:D.

Best Answer

You could try using a separate table (Sheet2) to do the replacing. Column A is the same as here (Sheet1), columns Sheet2!B:Z (or how many are maximally needed) are used to SPLIT the terms in Sheet1!B into them. For each column Sheet2!B:Z, create in a corresponding column Sheet2!AB:AZ an ARRAYFORMULA of VLOOKUPs exchanging the word via lookup in Sheet1!C:C to Sheet1!D:D.

=ARRAYFORMULA(IFNA(VLOOKUP(C3:AD, Sheet1!C2:D, 2, false),C3:AD))

Finally CONCATENATE Sheet2!AB:AZ into Sheet1!E.

Related Topic