Query, Index and FIlter

Question

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):

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.

player0 · Accepted Answer · 2019-02-12 01:12:52Z

1

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)

edited Feb 12, 2019 at 1:12

answered Feb 12, 2019 at 1:03

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan Sinigaglia Over a year ago

Yes, both worked fine. I couldn't think this way, it is great. Thanks a lot.

Collectives™ on Stack Overflow

Query, Index and FIlter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related