Formula to show the earliest date from a range with criteria

[TruffleOil]

I have a set of data (see picture attached), where different projects have several dates, even within same month (A3:B16). My goal is to build a ''calendar'' that will show Projects and their first date that will fit within the selected month.

E.g. in the picture: I want to see the projects that have dates that fall within January 2023 as per G3, but I don't want to see all their dates and repeated name of the projects. So for project A, which has multiple dates in January (B7:B10), I just want to see the first/earliest date from that project in January, which is 01/05/2023. Same applies to Project B.

Would appreciate if anyone could help to come up with a formula (maybe Index Match?) for D4 and E4!

Thank you!

 

[JamesCanale]

This seems too cumbersome to me.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G
2	Project
3	A	12/15/2022		Project	Date		1/1/2023
4	A	12/22/2022		A	1/5/2023
5	A	12/29/2022		B	1/8/2023
6	A	1/5/2023
7	A	1/12/2023
8	A	1/19/2023
9	A	1/26/2023
10	B	1/8/2023
11	B	1/16/2023
12	B	7/6/2023
13	B	7/13/2023
14	B	7/20/2023
15	B	7/27/2023
Sheet32

Cell Formulas
Range	Formula
E4:E5	E4	=MIN(IF(($A$3:$A$15=D4)*(MONTH($B$3:$B$15)=MONTH($G$3))*(YEAR($B$3:$B$15)=YEAR($G$3)),$B$3:$B$15,99999))

 

[Sufiyan97]

Another Option

Book3
	A	B	C	D	E	F	G	H	I	J
1
2	Source Data
3	Project Name	Date		Project Name	Date			January	2023
4	A	12/15/2022		A	1/5/2023			1/1/2023	1/31/2023
5	A	12/22/2022		B	1/8/2023
6	A	12/29/2022
7	A	1/5/2023
8	A	1/12/2023
9	A	1/19/2023
10	A	1/26/2023
11	B	1/8/2023
12	B	1/16/2023
13	B	7/16/2023
14	B	7/13/2023
15	B	7/20/2023
16	B	7/27/2023
17
18
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=INDEX($B$4:$B$16,AGGREGATE(15,6,(ROW($B$4:$B$16)-ROW($B$4)+1)/($B$4:$B$16>=$H$4)/($B$4:$B$16<=$I$4)/($A$4:$A$16=$D4),1))

 

[TruffleOil]

This seems too cumbersome to me.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G
2	Project
3	A	12/15/2022		Project	Date		1/1/2023
4	A	12/22/2022		A	1/5/2023
5	A	12/29/2022		B	1/8/2023
6	A	1/5/2023
7	A	1/12/2023
8	A	1/19/2023
9	A	1/26/2023
10	B	1/8/2023
11	B	1/16/2023
12	B	7/6/2023
13	B	7/13/2023
14	B	7/20/2023
15	B	7/27/2023
Sheet32

Cell Formulas
Range	Formula
E4:E5	E4	=MIN(IF(($A$3:$A$15=D4)*(MONTH($B$3:$B$15)=MONTH($G$3))*(YEAR($B$3:$B$15)=YEAR($G$3)),$B$3:$B$15,99999))

[/RANt

Cell Formulas
Range	Formula

Another Option

Book3
	A	B	C	D	E	F	G	H	I	J
1
2	Source Data
3	Project Name	Date		Project Name	Date			January	2023
4	A	12/15/2022		A	1/5/2023			1/1/2023	1/31/2023
5	A	12/22/2022		B	1/8/2023
6	A	12/29/2022
7	A	1/5/2023
8	A	1/12/2023
9	A	1/19/2023
10	A	1/26/2023
11	B	1/8/2023
12	B	1/16/2023
13	B	7/16/2023
14	B	7/13/2023
15	B	7/20/2023
16	B	7/27/2023
17
18
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=INDEX($B$4:$B$16,AGGREGATE(15,6,(ROW($B$4:$B$16)-ROW($B$4)+1)/($B$4:$B$16>=$H$4)/($B$4:$B$16<=$I$4)/($A$4:$A$16=$D4),1))

thank you for replying! the formulas work great, but I would also like to have a formula that selects the projects (D4) based on the same criteria as the dates (E4). for instance, there might be other projects in the source data, such as Project C, D, E, etc. that do not have any January dates, so from a big data source, I wouldn't know which ones would fit within the January period, sorry if I didn't elaborate that in the post. in these formulas I tried, it already assumes that the dates belong to those projects, but realistically I don't know which projects should be there either

 

[JamesCanale]

This is the part where I recommend excel 365. It would make quick work of this whole thing. Below is a bit of a mess.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G
2	Project
3	A	12/15/2022		Project	Date		1/1/2023
4	A	12/22/2022
5	A	12/29/2022		B	1/8/2023
6	A	1/5/2023		A	1/5/2023
7	A	1/12/2023
8	A	1/19/2023
9	A	1/26/2023
10	B	1/8/2023
11	B	1/16/2023
12	B	7/6/2023
13	B	7/13/2023
14	B	7/20/2023
15	B	7/27/2023
16	C	12/15/2022
17	C	2/2/2022
18	C	2/5/2023
19	C	2/10/2023
Sheet32

Cell Formulas
Range	Formula
D4:D17	D4	=IFERROR(LOOKUP(2,1/(COUNTIF($D$3:D3,IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,""))=0),IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,"")),"")
E4:E17	E4	=IFERROR(1/(1/(MIN(IF(($A$3:$A$19=D4)*(MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$B$3:$B$19,"")))),"")

 

[TruffleOil]

This is the part where I recommend excel 365. It would make quick work of this whole thing. Below is a bit of a mess.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G
2	Project
3	A	12/15/2022		Project	Date		1/1/2023
4	A	12/22/2022
5	A	12/29/2022		B	1/8/2023
6	A	1/5/2023		A	1/5/2023
7	A	1/12/2023
8	A	1/19/2023
9	A	1/26/2023
10	B	1/8/2023
11	B	1/16/2023
12	B	7/6/2023
13	B	7/13/2023
14	B	7/20/2023
15	B	7/27/2023
16	C	12/15/2022
17	C	2/2/2022
18	C	2/5/2023
19	C	2/10/2023
Sheet32

Cell Formulas
Range	Formula
D4:D17	D4	=IFERROR(LOOKUP(2,1/(COUNTIF($D$3:D3,IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,""))=0),IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,"")),"")
E4:E17	E4	=IFERROR(1/(1/(MIN(IF(($A$3:$A$19=D4)*(MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$B$3:$B$19,"")))),"")

oh wow it worked, thank you so much!! yes, I'm looking to update to office 365, it's much easier with formulas. what kind of formula would you use for office 365, out of curiosity?

 

[JamesCanale]

In 365 - exactly one formula solves the whole thing.

MrExcelPlayground14.xlsx
	A	B	C	D	E
1				1/1/2023
2	Project
3	A	12/15/2022		A	1/5/2023
4	A	12/22/2022		B	1/8/2023
5	A	12/29/2022
6	A	1/5/2023
7	A	1/12/2023
8	A	1/19/2023
9	A	1/26/2023
10	B	1/8/2023
11	B	1/16/2023
12	B	7/6/2023
13	B	7/13/2023
14	B	7/20/2023
15	B	7/27/2023
16	C	12/15/2022
17	C	2/2/2022
18	C	2/5/2023
19	C	2/10/2023
Sheet32

Cell Formulas
Range	Formula
D3:E4	D3	=LET(a,(FILTER(A3:B19,(MONTH(B3:B19)=MONTH(D1))*(YEAR(B3:B19)=YEAR(D1)))),b,SORT(SORT(a,2),1),c,UNIQUE(INDEX(a,,1)),d,XLOOKUP(c,INDEX(b,,1),INDEX(b,,2),,1),IF(ISODD(SEQUENCE(ROWS(d),2)),c,d))
Dynamic array formulas.

 

[Fluff]

Another option for 365

Fluff.xlsm
	A	B	C	D	E
1				01/01/2023
2	Project
3	A	15/12/2022		A	05/01/2023
4	A	22/12/2022		B	08/01/2023
5	A	29/12/2022
6	A	05/01/2023
7	A	12/01/2023
8	A	19/01/2023
9	A	26/01/2023
10	B	08/01/2023
11	B	16/01/2023
12	B	06/07/2023
13	B	13/07/2023
14	B	20/07/2023
15	B	27/07/2023
16	C	15/12/2022
17	C	02/02/2022
18	C	05/02/2023
19	C	10/02/2023
20
Data

Cell Formulas
Range	Formula
D3:E4	D3	=LET(u,UNIQUE(FILTER(A3:A100,(B3:B100>=D1)*(B3:B100<=EOMONTH(D1,0)))),HSTACK(u,MINIFS(B:B,A:A,u,B:B,">="&D1)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Data!$B$1:$B$19	D3