Return values based on date range

[Cubist]

Hi,
I have a lookup table on the left and data on the right. I'm wondering what's the best way to return the values (A, B, C, D) if the date is in the range on the left. If not found, return 0.

Book1.xlsm
	A	B	C	D	E	F
1	Begin Date	End Date	Return		Date	Return
2	2/1/21	1/31/22	A		5/31/19
3	2/1/22	1/31/23	B		6/30/19
4	2/1/23	1/31/24	C		7/31/19
5	2/1/24	1/31/25	D		8/31/19
6					9/30/19
7					10/31/19
8					11/30/19
9					12/31/19
10					1/31/20
11					2/29/20
12					3/31/20
13					4/30/20
14					5/31/20
15					6/30/20
16					7/31/20
17					8/31/20
18					9/30/20
19					10/31/20
20					11/30/20
21					12/31/20
22					1/31/21
23					2/28/21
24					3/31/21
25					4/30/21
26					5/31/21
27					6/30/21
28					7/31/21
29					8/31/21
30					9/30/21
31					10/31/21
32					11/30/21
33					12/31/21
34					1/31/22
35					2/28/22
36					3/31/22
37					4/30/22
38					5/31/22
39					6/30/22
40					7/31/22
41					8/31/22
42					9/30/22
43					10/31/22
44					11/30/22
45					12/31/22
46					1/31/23
47					2/28/23
48					3/1/23
Sheet1

 

[SunnyAlv]

Try this

=IF(
AND(E2>$A$2-1,E2<$B$2+1)=TRUE,$C$2,
IF(AND(E2>$A$3-1,E2<$B$3+1)=TRUE,$C$3,
IF(AND(E2>$A$4-1,E2<$B$4+1)=TRUE,$C$4,
IF(AND(E2>$A$5-1,E2<$B$5+1)=TRUE,$C$5,""))))

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	Begin Date	End Date	Return		Date	Return
2	2/1/2021	1/31/2022	A		5/31/2019	0
3	2/1/2022	1/31/2023	B		6/30/2019	0
4	2/1/2023	1/31/2024	C		7/31/2019	0
5	2/1/2024	1/31/2025	D		8/31/2019	0
6					9/30/2019	0
7					10/31/2019	0
8					11/30/2019	0
9					12/31/2019	0
10					1/31/2020	0
11					2/29/2020	0
12					3/31/2020	0
13					4/30/2020	0
14					5/31/2020	0
15					6/30/2020	0
16					7/31/2020	0
17					8/31/2020	0
18					9/30/2020	0
19					10/31/2020	0
20					11/30/2020	0
21					12/31/2020	0
22					1/31/2021	0
23					2/28/2021	A
24					3/31/2021	A
25					4/30/2021	A
26					5/31/2021	A
27					6/30/2021	A
28					7/31/2021	A
29					8/31/2021	A
30					9/30/2021	A
31					10/31/2021	A
32					11/30/2021	A
33					12/31/2021	A
34					1/31/2022	A
35					2/28/2022	B
36					3/31/2022	B
37					4/30/2022	B
38					5/31/2022	B
39					6/30/2022	B
40					7/31/2022	B
41					8/31/2022	B
42					9/30/2022	B
43					10/31/2022	B
44					11/30/2022	B
45					12/31/2022	B
46					1/31/2023	B
47					2/28/2023	C
48					3/1/2023	C
Sheet2

Cell Formulas
Range	Formula
F2:F48	F2	=IFERROR(VLOOKUP(E2:E48,A2:C5,3),0)
Dynamic array formulas.

 

[Peter_SSs]

Another possible option

24 01 18.xlsm
	A	B	C	D	E	F
1	Begin Date	End Date	Return		Date	Return
2	1/02/2021	31/01/2022	A		31/05/2019	0
3	1/02/2022	31/01/2023	B		30/06/2019	0
4	1/02/2023	31/01/2024	C		31/07/2019	0
5	1/02/2024	31/01/2025	D		31/08/2019	0
6					30/09/2019	0
7					31/10/2019	0
8					30/11/2019	0
9					31/12/2019	0
10					31/01/2020	0
11					29/02/2020	0
12					31/03/2020	0
13					30/04/2020	0
14					31/05/2020	0
15					30/06/2020	0
16					31/07/2020	0
17					31/08/2020	0
18					30/09/2020	0
19					31/10/2020	0
20					30/11/2020	0
21					31/12/2020	0
22					31/01/2021	0
23					28/02/2021	A
24					31/03/2021	A
25					30/04/2021	A
26					31/05/2021	A
27					30/06/2021	A
28					31/07/2021	A
29					31/08/2021	A
30					30/09/2021	A
31					31/10/2021	A
32					30/11/2021	A
33					31/12/2021	A
34					31/01/2022	A
35					28/02/2022	B
36					31/03/2022	B
37					30/04/2022	B
38					31/05/2022	B
39					30/06/2022	B
40					31/07/2022	B
41					31/08/2022	B
42					30/09/2022	B
43					31/10/2022	B
44					30/11/2022	B
45					31/12/2022	B
46					31/01/2023	B
47					28/02/2023	C
48					1/03/2023	C
Lookup letter

Cell Formulas
Range	Formula
F2:F48	F2	=XLOOKUP(E2:E48,A2:A5,C2:C5,0,-1)
Dynamic array formulas.