# Index and Count

#### ExcelTeacher

##### New Member
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).

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

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

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

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.

Replies
8
Views
293
Replies
3
Views
697
Replies
0
Views
328
Replies
5
Views
563
Replies
12
Views
2K

1,219,792
Messages
6,150,288
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back