index match array formula based on two criteria and from earliest to latest date

[mychi11]

artrecordsheet210520202.xlsm
	C	D	E	F	G	H						N									W								AE	AF
1	CY NO (1)	CY NO (2)	CY NO (3)	CRYO DATE	WIFE NAME	ART NO.						AVAILABLE EMB									AVAILABLE OC								STRAW BALANCE	LOCATION
2																														A-1C-1
3	6	8		28-05-20	BETTY	200522-01															18								6	A-1C-2
4																														A-1C-3
5	2			21-05-20	ALICE	200514-02						2																	1	A-1C-4
6	1			27-05-20	AMY	200514-01																							11	A-1C-5
7																														A-1C-6
8	3	4	5	18-05-20	ACE	200515-01						2																	1	A-1C-7
9																														A-1C-8
10																														A-1C-9
11	6	8		04-12-21	BETTY	200522-01						2																	1	A-1C-10
12																														A-1C-11
13	1			27-05-20	AMY	200514-01																								A-1C-12
STORAGE TANK

I am wondering if anyone could suggest me a formula for column C for the column to match both the cryo date and wife name in another sheet with the CY no (from the earliest date to later date) for Cy no (1,2,3)
I tried the following formula :
=IFERROR(INDEX(tableart[CY NO. ],AGGREGATE(15,6,(ROW(tableart[WIFE NAME])-ROW(ART!$O$2)+1)/(tableart[WIFE NAME]=$G3)*(ROW(tableart[CRYO DATE])-ROW(ART!$CK$2)+1)/(tableart[CRYO DATE]=$F3),COLUMNS($C3:C3))),"")

 

[jasonb75]

If you're dragging right then you need to enter the table column names twice to make them absolute.

You had a lot in the formula that you didn't need as well, so I've taken that out. Hopefully I have the syntax right.

=IFERROR(INDEX(tableart[[CY NO. ]:[CY NO. ]],AGGREGATE(15,6,ROW(tableart[[#Headers][WIFE NAME]:[WIFE NAME]])/(tableart[[WIFE NAME]:[WIFE NAME]]=$G3)/(tableart[[CRYO DATE]:[CRYO DATE]]=$F3),COLUMNS($C3:C3))),"")

 

[mychi11]

thank you so much for your ans. I have realised some problem with my logic previously as i might have multiple cryo dates in this case. so i might to the match another criteria instead. I tried to modify your formula according to my need but i was having some difficulties. I was wondering if you could help me for the following case?

artrecordsheet210520202.xlsm
	C	D	E	F	G						M									V								AD	AE
1	CY NO (1)	CY NO (2)	CY NO (3)	WIFE NAME	ART NO.						AVAILABLE EMB									AVAILABLE OC								STRAW BALANCE	LOCATION
2																													A-1C-1
3	6	8		BETTY	200522-01															18								6	A-1C-2
4																													A-1C-3
5	2			ALICE	200514-02						2																	1	A-1C-4
6	1			AMY	200514-01																							11	A-1C-5
7																													A-1C-6
8	3	4	5	ACE	200515-01						2																	1	A-1C-7
9																													A-1C-8
10																													A-1C-9
11	6	8		BETTY	200522-01						2																	1	A-1C-10
12																													A-1C-11
13	1			AMY	200514-01																								A-1C-12
STORAGE TANK

artrecordsheet210520202.xlsm
	A	B	C	D	E	F
1	CY NO.	WIFE NAME	ART NO.	CRYO DATE	LOCATION	LOCATION 2
2	1	AMY	200514-01	19-5-2020	0	0
3	2	ALICE	200514-02	21-5-2020	A-1C-4	0
4	3	ACE	200515-01	18-5-2020	A-1C-7	0
5	4	ACE	200515-01	26-6-2020	A-1C-7	0
6	5	ACE	200515-01	27-8-2020	A-1C-7	0
7	6	BETTY	200522-01	28-5-2020	A-1C-2	0
8	7
9	8	BETTY	200522-01	15-8-2020	A-1C-2	0
10	9	AMY	200514-01	27-5-2020	A-1C-5	A-1C-12
11	10	BETTY	200522-01	4-12-2021	A-1C-10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
CY NO

I was having problem to show the cy no for betty and amy correctly. I will need them to match according to the location in this. I am wondering if anyone could suggest me a formula for column C to E in this case?

 

[jasonb75]

I think this is what you're asking for. It would help if your examples contained the correct (expected) results instead of incorrect formulas.

=IFERROR(INDEX(tableart[[CY NO. ]:[CY NO. ]],AGGREGATE(15,6,ROW(tableart[[#Headers][WIFE NAME]:[WIFE NAME]])/(tableart[[WIFE NAME]:[WIFE NAME]]=$G3)/(tableart[[CRYO DATE]:[CRYO DATE]]=$F3)/(((tableart[[Location]:[Location]]=$AE3)+(tableart[[Location 2]:[Location 2]]=$AE3))>0),COLUMNS($C3:C3))),"")

 

[mychi11]

thanks it works perfectly!