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