IF statement and Column Search help

[danigirl121]

Link


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

<bdo dir="ltr">=INDEX(Student,MATCH("Teacher 2",Teacher,0))

<bdo dir="ltr">=IF($A$2=INDEX(Student,MATCH("Teacher 2",Teacher,0)),OFFSET(INDEX(Student,MATCH("Teacher 2",Teacher,0)),1,0),"")</bdo>
</bdo>​

The link is to the Google Spreadsheet I'm referencing. The first formula is put into the A2 cell of Teacher 2's sheet, it pulls the first student that is assigned to her. And it does this successfully.
The second formula I'm trying to get to pull the next student that belongs to Teacher 2, but it just pulls the next student from the list.
I also don't think the logical expression of my IF statement is correct because I need it to take into account all the student names above the one I'm trying to pull. I feel like there should be a COUNTIF statement, but I haven't been able to get it to work.

 

[tygrrboi]

{=INDEX($A$1:$A$16,SMALL(IF($G$1:$G$16=$C$21,ROW($A$1:$A$16)-ROW($A$1)+1),2))}

input with CONTROL + SHIFT + ENTER to make the {} and input it as an array formula. Do not manually type the {}

replace the big blue 2 with any number to find the 3rd, 4th, 5th etc.

The $C$21 reference is where I put the teachers name I wanted to lookup. In that cell, it just says "Teacher B" You can change this to an absolute reference of anywhere you like, or text in quotation marks if you like.

 

[tygrrboi]

A way to make your life easier when dragging down (lets say you want 1st, 2nd, 3rd, 4th, etc students in each consecutive row) is to replace the blue number with a row reference. (or Column reference for dragging across)

For example:
If I want the 2nd match...
H2= {=INDEX($A$1:$A$16,SMALL(IF($G$1:$G$16=$C$21,ROW($A$1:$A$16)-ROW($A$1)+1),Row()))} (because the row of H2 = 2, it will return the second match)
or
H3= {=INDEX($A$1:$A$16,SMALL(IF($G$1:$G$16=$C$21,ROW($A$1:$A$16)-ROW($A$1)+1),Row()-1))} (because the row of H3 is 3, 3-1 will return the second match)

Drag that down and you will get 2nd match, then 3rd, then 4th... without modifying the equation.