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-seperated. 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      Socratespla      Platodes      Descatesheg      Hegelheg,des  Hegel, DescartesI'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 here: https://docs.google.com/spreadsheet/pub?key=0Ap6uG4C3OXPtdG1zakM3TG9sc210dVhRaWhPRmRGY0E&output=html(In the MWE you can also find an attempt with FILTER, but it gives the 1xN or Nx1 error, which makes perfect sense...)Thanks so much for your help in advance!-Gauwain
Index-Match on Arrayformula
google spreadsheets
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    | socPlato       | 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.