I am looking in a particular column for names and a want to return the data
below the name to a second sheet. I have used =MATCH(D1,Apr!$AG$1:$AG$658,0)
which will return the row number where the lookup name appears. I have also
used the index function to return the information in the cell but doing it
that way I would have to change the formula in each cell downward. I know
there is a better way.

Thanks for any help.

Sheet 1
Column A
Name1
data1
data2
data3
Name2
data1
data2
data3

Sheet 2
A B
Name1 Name 2
data1 data1
data2 data2
data3 data3