Looking up A name in a table and reurning all rows that are associ

[Guest]

I am trying to lookup a persons name in a table. Then return ALL the rows
that are associated with that name. HELP.
IE:
Sheet1-A2 has the name I'm looking for
The array is on Sheet2-A1:G16
The name shows only one in the sheet2 column A ( like a pivit table)
The information I need is in that row and 3-4 rows below in column B
What I need is something like a Vlookup then Offset to find the rows below
the found name.

 

[T. Valko]

Do you mean your data table looks like this:

name1.....x
................y
................z
name2.....x
................y
................z

You want to lookup the name in column A and return the data from column B.
Is the amount of data in column B the same for every name?

Biff

 

[Guest]

Yes that is correct.

Do you mean your data table looks like this:

name1.....x
................y
................z
name2.....x
................y
................z

You want to lookup the name in column A and return the data from column B.
Is the amount of data in column B the same for every name?

Biff

 

[T. Valko]

With this table on Sheet2 in the range A1:B6

name1.....x
................y
................z
name2.....x
................y
................z


Sheet1 A2 = lookup name = name2

Enter this formula and copy down a total of 3 cells:

=INDEX(Sheet2!B$1:B$6,MATCH(A$2,Sheet2!A$1:A$6,0)+ROWS($1:1)-1)

Biff