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

  • 1
    This question has been bumped by Community User 11 times. Oct 18, 2023 at 20:44
  • Also lacks the information necessary to answer.
    – Blindspots
    Oct 18, 2023 at 23:48

1 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("/");
  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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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