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.

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.

| improve this answer | |
  • 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 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. – Felix Dombek Oct 29 at 13:02
  • thank you so much, you have no idea how much you just helped me! – David S Oct 29 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.