INDEX MATCH in array formula

Question

I'm making a database in Google Drive. In one column I have abbreviations of a number of departments someone is in, but people can be linked to multiple departments. The abbreviations are comma-separated. I would like to have a formula that SPLITs the abbreviations at [,], then MATCHes the results to a range, and returns the full names of the departments.

abb      desired result
---      --------------
soc      Socrates
pla      Plato
des      Descates
heg      Hegel
heg,des  Hegel, Descartes

I've tried an arrayformula with INDEX and MATCH to no avail:

=join(", ";arrayformula(index(B$2:$D$7;match(split(F2;",");D$2:D$7;0);2)))

I've published a MWE where you can also find an attempt with FILTER, but it gives the 1xN or Nx1 error, which makes perfect sense ...

Answer 1

This seems to be easiest solved with a script. If you have a list of philosopher names (column A) and their abbreviations (column B):

Name        | Abbreviation
--------------------------
Socrates    | soc
Plato       | pla
...

in a sheet named Philosophers, you should be able to use the following script function:

function getPhilosophersByAbbreviations(abbreviationsStr) {
  var philosophers = SpreadsheetApp.getActive().getSheetByName("Philosophers").getDataRange().getValues();
  var result = new Array();
  var abbreviations = abbreviationsStr.split(",");
  for (var a = 0; a < abbreviations.length; a++) {
    var abbreviation = abbreviations[a];
    for (var p = 0; p < philosophers.length; p++) {
      if (philosophers[p][1] == abbreviation) {
        result.push(philosophers[p][0]);
      }
    }
  }
  return result;
}

In your data sheet, you enter the formula in column B:

soc     | =getPhilosophersByAbbreviations(A1)
pla     | =getPhilosophersByAbbreviations(A2)
des     | =getPhilosophersByAbbreviations(A3)
heg     | =getPhilosophersByAbbreviations(A4)
heg,des | =getPhilosophersByAbbreviations(A5)

... and the full names will be displayed in columns B and so on.

Feel free to play with the example spreadsheet I set up.

Answer 2

A array formula (does not need to be, but appears to have been requested) that splits pairs up. Presumably could be extended to more than two departmental abbreviations in single cell, but with GAS already provided that would seem masochistic:

=ArrayFormula(INDEX($D$2:$D$7,MATCH(SPLIT($J2,","),$E$2:$E$7,0))&IFERROR(", "&INDEX($D$2:$D$7,MATCH(INDEX(SPLIT($J2,","),,2),$E$2:$E$7,0)),""))

Assumes the lookup table is in D2:E7 and the values to be searched for in J2:J6. The formula may be copied down from anywhere in Row2.