Google Sheets – INDEX MATCH in Array Formula

google sheetsgoogle-apps-script

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 …

Best Answer

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.

Related Topic