Google-sheets – Searching a list and replacing value if any item matches any item in the other list in Google Sheets

As discussed in the thread you linked to in comments, the formula solution is quite convoluted and potentially very slow, if there are multiple instances to be replaced in the string. However with your qualification:

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I believe the solution becomes a little more straight forward (and reasonably efficient, I would say):

=IFERROR(REGEXREPLACE(A2;JOIN("|";Lookup!$A$2:$A$5);VLOOKUP(REGEXEXTRACT(A2;JOIN("|";Lookup!$A$2:$A$5));Lookup!$A$2:$B$5;2;0)&"");A2)

which has the added advantage of being able to be easily converted into an array formula that will populate down the column:

=ArrayFormula(IFERROR(REGEXREPLACE(A2:A;JOIN("|";Lookup!A2:A5);VLOOKUP(REGEXEXTRACT(A2:A;JOIN("|";Lookup!A2:A5));Lookup!A2:B5;2*SIGN(ROW(A2:A));0)&"");A2:A))

I have also entered a custom function solution:

function MSUBSTITUTE(text, subTable)
{
  var searchArray = [], subArray = [];
  for (var i = 0, length = subTable.length; i < length; i++)
  {
    if (subTable[i][0])
    {
      searchArray.push(subTable[i][0]);
      subArray.push(subTable[i][1]);
    }
  }
  var re = new RegExp(searchArray.join('|'), 'g');
  return text.replace(re, function (match) {return subArray[searchArray.indexOf(match)];});
}

=MSUBSTITUTE(A2;Lookup!$A$2:$B$5)

It would be fairly trivial to convert this to an auto-populating array formula, as well.

Not sure there is a way to display a matching value base on two criteria - wouldn't be the first time I was wrong though.

In the meantime, you could create three queries and query each week individually, then you could do a vlookup to get the data into your table. I created a Google Spreadsheet. You should be able to edit this, etc.

If you want to do more automation, for example, have the report run every Monday morning and have it sent to your email, you can use my blog post and scroll down to the Using Triggers and Classes to Automate your Google Analytics Spreadsheet Delivery section.

Best of luck!