a) Can you not just use Autofilter? If you turn autofiltering on, you can
select the specific value ("MOORE") in the "P1Teach" column, and get a list
of students that match.

b) You could create a pivot table of your data on a separate sheet and
manipulate the data in many ways.

HTH,

Eric


"Allen" wrote:

Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.