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.

1 Answer 1


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.

  • thank you for your response. I have everything in a separate sheet and split but I'm having trouble with the ARRAYFORMULA of VLOOKUPs formula. Would you mind giving me the formula for the first split column and I can run with it from there?
    – David S
    Oct 29, 2020 at 2:11
  • @DavidS I edited your spreadsheet and also added the formula to this answer. Turns out you don't even need separate ones for separate columns, it just works in 2 dimensions as well. The SPLIT and CONCATENATE can also be done via arrayformulas, so no manual work should be needed there. Oct 29, 2020 at 13:02
  • thank you so much, you have no idea how much you just helped me!
    – David S
    Oct 29, 2020 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.