Index and Count

[ExcelTeacher]

Hi there,

I have a worksheet where the name of the workshop, teacher and students are all listed in different collumns. I want to Find a teacher and list all of his students (4 groups) so I can make a worksheet as a report. Unfortunately the workshop is entered with names of students in collumn I (I2 to I300) and teachers names are in collumns M, Q, U, Y and AC).

Thanks for your help

 

[ghrain22]

I'm not sure what you're trying to accomplish. Is the problem that you want to perform a look up by teacher name, but the teachers column is to the right of the students?

Could you provide examples of sample data and desired output.

Somethings you may want to consider are:
Index Match Formulas
Pivot table

 

[ExcelTeacher]

Yes I want to perform a lookup by teacher name and the teachers columns are to the right of the students.

Sample Data :
I M Q U Y AC
1 Student names Teacher per1 Teacher per2 Teacher per3 Teacher per4 Teacher per5
2 Robert Mr. A Mr. C Mr. E Mrs. G
3 James Mr. A Mr. D Mr. F Mrs. G Mr. C
4 Bill Mrs. B Mr. A Mr. C Mr. H
5 Jane Mr. C Mrs. B Mr. A Mrs. G Mr. H

Desired Output
Mr. A
Robert
James
Bill
Jane

-page break-
Mrs. G
Robert
James
Jane

 

[Mike Szczesny]

Let's keep it simple. Assume you have a range A1:E4

1 a b c d 2 a x e f 3 m c g h 4 a b c e <colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

A6: a
a7: =index($A$1:$A$4,small(if($B$1:$E$4=$A$6,row($B$1:$E$4$)-row($B$1)+1),rows($A$7:A7))) press Cntrl+Shift+Enter, copy down

 

[ExcelTeacher]

Let's keep it simple. Assume you have a range A1:E4

1 a b c d 2 a x e f 3 m c g h 4 a b c e <tbody> </tbody>

<tbody>
</tbody>

A6: a
a7: =index($A$1:$A$4,small(if($B$1:$E$4=$A$6,row($B$1:$E$4$)-row($B$1)+1),rows($A$7:A7))) press Cntrl+Shift+Enter, copy down

Thank you for your help. But this formula doesn't work. Excel tells me that there are too many errors.