How to automatically update a sheet with data from the Main Sheet containing identical cells

[JacquesDB]

Good day

I have a main sheet where I update with wildlife breeding information. We have several fathers (each with their own sheet based on their names) and each father have several offspring past and yet to be born.

Each offspring gets a tag number, gender, date etc.

All this information is typed on the main sheet, from here I want the information linked to another sheet so that it pulls the information through based on the fathers name to that fathers respective sheet.

I have tried index/match but because one father has multiple offspring with different tags, it keeps on only pulling through the first tag it finds to all the other cells, (I could be doing it wrong or missing something), and VLookup seems to be doing the same, where it only matches the first row it finds on the main sheet and allocates that one value to all the rows in the fathers' respective sheets, ignoring the data following.

I hope that I have explained this good enough, if not, please feel free to ask or correct me.

Much appreciated in advance

Kind Regards

 

[Toadstool]

Hi JacquesDB,

You could use SMALL or the SMALL option of AGGREGATE to fetch 1st match, 2nd match, etc.

So if this is your Master sheet

JacquesDB.xlsx
	A	B	C	D
1	Master
2
3	Father	Offspring Tag	Gender	Birthdate
4	Othello	Butch	M	01-Jan-20
5	Mercy	Sarah	F	02-Feb-20
6	Othello	Bill	M	03-Mar-20
7	Tim	Tom	M	05-May-21
8	Othello	Nate	M	03-Mar-21
9
Master

Your Father sheet could be

JacquesDB.xlsx
	A	B	C	D
1	Othello	Offspring	Gender	Birthdate
2		Butch	M	01-Jan-20
3		Bill	M	03-Mar-20
4		Nate	M	03-Mar-21
5
6
7
Othello

Cell Formulas
Range	Formula
B2:D4	B2	=IFERROR(INDEX(Master!B$4:B$9999,AGGREGATE(15,6,ROW(Master!$A$4:$A$9999)-ROW(Master!$A$3)/(Master!$A$4:$A$9999=$A$1),ROW()-ROW($B$1))),"")