Google-sheets – Array formula with index match to return array of matches

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.

I would enter

=array_constrain(filter(C$2:C$5, (A$2:A$5 <= A8) * (B$2:B$5 >= A8)), 1, 1)

in cell B8, and copy it down the results array. Explanation:

• filter keeps only the cells from C where A8 falls between A and B.
• array_constrain makes sure at most one result is returned (this may be unnecessary if you are sure the ranges in the lookup array do not overlap).