pls help rgding VLOOK or MATCH INDEX

[dingdang]

I have below data in 1 sheet..

[pre]

CENTER NO	id no	student name	standard	Class no
1011	2122	mohan	X	A
1011	55455	rajesh	V	B
1011	5454	mad	VII	C
1011	45212	moh	IIV	D
494	5455	d'souza	X	A
494	7454	randson	X	A
494	87854	semon	X	A
511	21	vicky	X	A
511	21245	samrat	V	B
511	3232	santo	V	B
511	95646	madhur	V	B
511	16349	abhishek	VII	C
511	16569	amir	VII	C
1546	15479	rajesh	VI 	E
1546	87655	mohan	IIV	D
in second sheet if i input center no in B2 required only all data pertains to center no in B4 like ..

CENTER NO	id no	student name	standard	Class no
1011	2122	mohan	X	A
1011	55455	rajesh	V	B
1011	5454	mad	VII	C
1011	45212	moh	IIV	D

[/pre]
have tried vlookup but not work. Pls help

 

[koi]

hi, maybe it is because center no is not unique number,


just add 1 helper column combine center no & id no and dont forget to add $ sign since it is in 1 workbook.

 

[Sammy]

dingdang,


Check on the below link for an uploaded file.


http://rapidshare.com/files/68853277/dingdang.xlsx


Thanks,

Sammy

 

[Faseeh]

Hi dingdang,


Please enter this in C5 in second sheet, press ctrl+shift+enter and drag to bottom/right:

=OFFSET('RAW Data'!$A$2,SMALL(IF('RAW Data'!$B$2:$B$16=Input!$D$2,ROW('RAW Data'!$B$2:$B$16)-1),ROW(A1))-1,COLUMN(A1)-1)

Regards,