Matching ID and Date where date only matches month/year

[Wishmaster89]

Hi All

I have two sets of ID's and dates that i want to return the period code for. The dates are usually out by 1 day or 2(rarely) so I want to match the ID and month/year.

Can anyone help please?

This is what i currently have

=INDEX(C1:C5,MATCH(A1&B1,F:F&G:G,0))

Book1
	A	B	C	D	E	F	G	H
1	ID	Date 1	Period			ID	Date 2	Return period where month/year matches
2	1	03/10/2022	R1			1	04/10/2022	#N/A
3	2	10/04/2022	R2			2	11/04/2022
4	3	29/03/022	R3			3	30/03/2022
5	4	01/08/2021	R4			4	02/08/2022
Sheet1

Cell Formulas
Range	Formula
H2	H2	=INDEX(C1:C5,MATCH(A1&B1,F:F&G:G,0))

 

[jtakw]

Hi,

Try it this way:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	ID	Date 1	Period			ID	Date 2	period where ID & month/year matches
2	1	10/3/2022	R1			1	10/4/2022	R1
3	2	4/10/2022	R2			2	4/11/2022	R2
4	3	3/29/2022	R3			3	3/30/2022	R3
5	4	8/1/2022	R4			4	8/2/2022	R4
Sheet1071

Cell Formulas
Range	Formula
H2:H5	H2	=LOOKUP(2,1/((A$2:A$5=F2)*(MONTH(B$2:B$5)=MONTH(G2))*(YEAR(B$2:B$5)=YEAR(G2))),C$2:C$5)

 

[Wishmaster89]

Thanks @jtakw

That worked!

What does the 2,1 do bit do, i can't get my head around it.

 

[jtakw]

Thanks @jtakw

That worked!

What does the 2,1 do bit do, i can't get my head around it.

You're welcome, thanks for the feedback.

LOOKUP(2,1/((A$2:A$5=F2)*(MONTH(B$2:B$5)=MONTH(G2))*(YEAR(B$2:B$5)=YEAR(G2))),C$2:C$5)

Red part, we built an array based on certain conditions, 1 is divided by this array to produce #DIV/0! error for the Non-matched
This array now consists of 1 for matched, and #DIV/0! for non matched, LOOKUP ignores the errors.
LOOKUP is looking for 2, but only finds the 1, so results in the corresponding value in GREEN.