# Lookup

G

#### Guest

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

D

#### Domenic

Assuming that the format is consistent, try...

A1, copied down and across:

=INDEX(Sheet1!\$A\$1:\$A\$8,(ROWS(A\$1:A1)-1)+(COLUMNS(\$A1:A1)*4-4+1))

Hope this helps!

G

#### Guest

I tried to figure out how I could get it to work for my situation but but was
unsuccessful.
This is what I have but it isn't working:
=INDEX(Apr!\$AG\$1:\$AG\$658,MATCH(B\$1,Apr!\$AG\$1:\$AG\$658,0)-1)+(COLUMNS(\$AG1:AG1)*4-4+1))

I am doing a Match to find the row number of the Name I am searching for
because they will not be in the same order in both sheets.

Once I find the name the data would be in the same order from sheet to sheet.

Also

D

#### Domenic

A little unclear, but assuming that B1, C1, D1, etc., contain the name,
try...

B2, copied down and across:

=INDEX(Apr!\$AG\$1:\$AG\$658,MATCH(B\$1,Apr!\$AG\$1:\$AG\$658,0)+ROWS(B\$2:B2))

Hope this helps!

G

#### Guest

That worked, thanks alot!

Domenic said:
A little unclear, but assuming that B1, C1, D1, etc., contain the name,
try...

B2, copied down and across:

=INDEX(Apr!\$AG\$1:\$AG\$658,MATCH(B\$1,Apr!\$AG\$1:\$AG\$658,0)+ROWS(B\$2:B2))

Hope this helps!