1

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 ...

0

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.

  • That looks like something that could work, Vidar! I'll test it monday. I had hoped for a non-script solution. But if this works, I'll be very content! When it does, I'll be sure to mark your answer as 'accepted'. Thanks in advance for you time! – Gauwain Nov 9 '13 at 7:48
  • That worked wonders! Thanks. I did use =join(", ";getPhilosophersByAbbreviations(A5)) however, to prevent the output as an array. Looking back, that's exactly what I asked though. ;) – Gauwain Nov 14 '13 at 14:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.