ARRAYFORMULA and FILTER

Question

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing

In the first sheet ("Matrix"), I have a matrix which associate Ref with Tests. In the second sheet ("Results") , I have a recap of the different Ref, and for each Ref, I want to see the associated Tests. For now, I manage to do it using a formula which uses FILTER (the function is in the "associated tests" cell).

But, in order to be able to add new ref without to have to copy the formula, I wanted to use an ARRAYFORMULA. And, obviously, it doesn't work with my actual formula because of the FILTER.

Does anyone have an idea on a way to achieve this ? Maybe QUERY could help me but I don't really know its capabilities

Answer 1

I solved my issue using a custom formula :

function getTests(reqRefs, matrix) {
  let outputArray = [];
  reqRefs.forEach(function(reqRef) {
    if (reqRef != "") {
      let column = matrix[0].indexOf(reqRef.toString()); // Index of the column of the requirement
      let replies = matrix.map(item => item[column] == "X"? item[0] : null).filter(item => item != null ).join("/");
      outputArray.push(replies);
    }
  });
  return outputArray;
}

The first parameter for this formula is column range with the ref for which I want the tests. The second is the matrix.