Query, Index and FIlter

1
votes

I have a school project and I need to filter students by a teacher. I have a random list where each student has a teacher assigned (G2:H100) and I need to create new sheets, one for each teacher, but my way is not working as I would like to. I have done this sheet for example, in just one tab (figure 1):

figure 1

This is the formula at cell L2:

=QUERY(FILTER((G2:H100);INDEX((G2:H100);0;2)=H2);"Select Col1")

At the blue box, I have exactly what I wanted: Maria's student names listed on each row.

I don't know why I can't repeat the same results at green and brown boxes. I have the same formulas at both of them, just changing =H2 to =H3 and =H4, respectively.

The results: the names for each teacher at the same cell, not listed one row below the other, a blue box. I have tried other ways, but I could find a solution. I would like someone to help me figure out what is going on.

1
google-sheetsgoogle-sheets-querygoogle-sheets-formulagoogle-query-language

1 Answers

1
votes

try these:

  • L2: =QUERY($G$2:$H$100; "select G where H='"&K2&"'"; 0)
  • L12: =QUERY($G$2:$H$100; "select G where H='"&K12&"'"; 0)
  • L20: =QUERY($G$2:$H$100; "select G where H='"&K20&"'"; 0)

in case there would be an empty output use these:

  • L2: =QUERY(ARRAYFORMULA(TO_TEXT($G$2:$H$100)); "select Col1 where Col2='"&K2&"'"; 0)
  • L12: =QUERY(ARRAYFORMULA(TO_TEXT($G$2:$H$100)); "select Col1 where Col2='"&K12&"'"; 0)
  • L20: =QUERY(ARRAYFORMULA(TO_TEXT($G$2:$H$100)); "select Col1 where Col2='"&K20&"'"; 0)