Array index problems, need assistance or any advice to help to create formula to enable to operate worksheet.

[teresafinance]

Hi All, I am trying to figure out a formula that find the data of families with one, two, three, four, or five children in our database. In the first worksheet is where the data is compiled. Column 1 - Family ID, Colum 2 - No of children in Family, and column 3 Student IDs relevant to each family. In worksheet 2 I have the same data that is extracted from our system on a ms query that has the Student ID listed.

I have attached an example of what I am working with. What formula would I need to put into cell c3:C1500 to obtain the information needed? I know very little about Index and arrays and what I have tried thus far is not working and returns a #N/A result.

 

[Peter_SSs]

OK, see if this helps.

teresafinance_1.xlsx
	A	B	C
1	Family ID	Student ID	Student Name
2	2022	1000	Adam
3	2022	1001	Aarron
4	2023	1002	Peter
5	2023	1003	Rachel
6	2841	1004	Phoebe
7	2841	1005	Mark
8	2405	1006	Calum
9	2026	1007	Aiden
10	2378	1008	Myra
11	2377	1009	Petra
12	2378	1010	Michael
13	2028	1011	Ollie
14	2691	1012	Kaden
15	2331	1013	Ryan
Sheet2

teresafinance_1.xlsx
	A	B	C
1	Family ID	No of Children	Student ID
2	2022	2	1000
3	2022	2	1001
4	2023	2	1002
5	2023	2	1003
6	2841	2	1004
7	2841	2	1005
8	2405	3	1006
9	2026	2	1007
10	2378	3	1008
11	2377	1	1009
12	2378	3	1010
13	2028	3	1011
14	2691	2	1012
15	2331	1	1013
Sheet1

Cell Formulas
Range	Formula
C2:C15	C2	=INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!B$2:B$15)/(Sheet2!A$2:A$15=A2),COUNTIF(A$2:A2,A2)))