Google-sheets – Is it possible to use multiple formats within a single (formula-based) cell

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.

Please try:

 =indirect(E26&"!"&$AV$6)  

Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).