List of names using Transpose with Vlookup?

[Safari Breeze]

Hi,
Any help appreciated!
Sheet 2 has a range of vertical data in Columns A & B: 'Class' & 'Names'
Sheet 1 has the 'Class' code in cell:G2 and list of names range from D4

I want a formula which uses the 'Class' code from G2 to return a horizontal list of all names (preferably alphabetically) belonging to that code.

Does anyone know what that formula should be?

My failed attempt is:

(D4)=TRANSPOSE(VLOOKUP(G2,Sheet2!$A2:B50,2,FALSE))

This returns a name from the correct class code, but when I drag the formula (D4) horizontally for more names, I get the same name again, again and again!!!
(Excel 2007)

 

[MikeWx]

The following formula in Cell D4 will return all the names from Sheet 2 that match the Class value in Cell G2 of Sheet 1. It is an array formula that requires you to press Control-Shift-Enter instead of just Enter. After you copy it across horizontally, it will display blank cells when it runs out of matching names.

=IFERROR(INDEX(Sheet2!$B$2:$B$50,SMALL(IF(ISERROR(MATCH(Sheet2!$A$2:$A$50,$G$2,0)),FALSE,ROW(Sheet2!$A$2:$A$50)-1),COLUMN(A3))),"")

 

[Safari Breeze]

Thanks MikeWx for reply,

I dont know why I cant get an array to work for this formula, despite pressing Cltr,Shift + Enter. I had to change sheet2 to 'Class Lists', the actual name of sheet.

Is there something I'm missing here?

=IFERROR(INDEX(Class Lists!$B$2:$B$50,SMALL(IF(ISERROR(MATCH(Class Lists!$A$2:$A$50,$G$2,0)),FALSE,ROW(Class Lists!$A$2:$A$50)-1),COLUMN(A3))),"")

 

[Yahya]

Try this

Excel Workbook
	A	B
1	Class	Name
2	Class A	A
3	Class B	B
4	Class C	C
5	Class D	A
6	Class A	B
7	Class B	C
8	Class C	A
9	Class D	B
10	Class A	C
11	Class B	A
12	Class C	B
13	Class D	C
14	Class A	A
15	Class B	B
16	Class C	C
17	Class D	A
18	Class A	B
19	Class B	C
20	Class C	A
21	Class D	B
22	Class A	C
23	Class B	A
24	Class C	B
25	Class D	C
Class Lists

Excel 2010

and in sheet 1

Excel Workbook
	G	H	I	J	K	L	M	N
2	Class A
3	A	B	C	A	B	C
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Safari Breeze]

Mike X

 

[Safari Breeze]

Thanks a lot MikeWx and Yahya...those formulas both worked...BRILLIANT...i'll be able to sleep better after that.

Cheers and thanks again!

 

[Yahya]

Thanks a lot MikeWx and Yahya...those formulas both worked...BRILLIANT...i'll be able to sleep better after that.

Cheers and thanks again!

you are welcome