Google Sheets – Array Formula with N/A to Print Nicely When Empty

You were so close !!!!!!!!

Use the following formula.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.

I would write a custom function for this, to be used as

=combinations(table1!B2:B, table1!C2:C, table2!A2:A, table2!C2:C)

that is, the parameters are ValueColumn1, KeyColumn1, ValueColumn2, KeyColumn2.

The logic is entirely straightforward: run a double loop and collect whatever matches.

function combinations(range1, key1, range2, key2) {
  var output = [];
  if (range1.length != key1.length || range2.length != key2.length) {
    throw new Error('Each range must have the same length as its key'); 
  }
  for (var i = 0; i < range1.length; i++) {
    for (var j = 0; j < range2.length; j++) {
      if (key1[i][0] === key2[j][0] && key1[i][0] !== '') {
        output.push([range1[i][0], range2[j][0]]);
      }
    }
  }
  return output;
}