Formula to check a given date in the range of dates and return corresponding value (No VBA)

[EmadMassoud]

I am looking for a Formula to check a specific date (e.g today's date) in a range of Dates (E6:E18 & G6:G18) and return the corresponding value from an array (C6:C18) e.g. Block 6. Can I use INDEX & MATCH or VLOOKUP and which is better and why. Thank you

 

[Toadstool]

Hi EmadMassoud,

You could just use SUMIFS

EmadMassoud.xlsx
	C	D	E	F	G
1			Result
2			6
3
4
5	Block		Start		End
6	1		01-Jul-21		01-Aug-21
7	2		02-Aug-21		29-Aug-21
8	3		30-Aug-21		26-Sep-21
9	4		27-Sep-21		24-Oct-21
10	5		25-Oct-21		21-Nov-21
11	6		22-Nov-21		19-Dec-21
12	7		20-Dec-21		16-Jan-22
13	8		17-Jan-22		13-Feb-22
14	9		14-Feb-22		13-Mar-22
15	10		14-Mar-22		10-Apr-22
16	11		11-Apr-22		08-May-22
17	12		09-May-22		05-Jun-22
18	13		06-Jun-22		03-Jul-22
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIFS($C$6:$C$18,$E$6:$E$18,"<="&TODAY(),$G$6:$G$18,">="&TODAY())
F2	F2	=IF(E2=0,"Today is out of range","")
E7:E18	E7	=G6+1
G6	G6	=E6+31
G7:G18	G7	=E7+27

 

[Alex Blakenburg]

Below are a couple of other options:
My first thought was that since your dates are consecutive, that you could simply use Vlookup with a True (approximate match). This is ruled out by the fact that you need to look up a column to the left.
You can achieve the same thing using a Index/Match with the match type of less than. The only issue might be if the test date is greater than the 03-Jul-22 in the table put together by Toadstool.
It also would not work if there were breaks in the dates ie the start date did not follow on from the previous end date.

The Lookup will handle multiple criteria in any version of Excel.
If you have 365 you would have more options.

20211126 Lookup Between Dates.xlsx
	C	D	E	F	G
1			Toadstool Result	Index Match	Lookup
2			6	6	6
3
4
5	Block		Start		End
6	1		1/07/2021		1/08/2021
7	2		2/08/2021		29/08/2021
8	3		30/08/2021		26/09/2021
9	4		27/09/2021		24/10/2021
10	5		25/10/2021		21/11/2021
11	6		22/11/2021		19/12/2021
12	7		20/12/2021		16/01/2022
13	8		17/01/2022		13/02/2022
14	9		14/02/2022		13/03/2022
15	10		14/03/2022		10/04/2022
16	11		11/04/2022		8/05/2022
17	12		9/05/2022		5/06/2022
18	13		6/06/2022		3/07/2022
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIFS($C$6:$C$18,$E$6:$E$18,"<="&TODAY(),$G$6:$G$18,">="&TODAY())
F2	F2	=INDEX($C$6:$C$18,MATCH(TODAY(),$E$6:$E$18,1),0)
G2	G2	=LOOKUP(2,1/ (($E$6:$E$18<=TODAY())*($G$6:$G$18>=TODAY())),$C$6:$C$18)
E7:E18	E7	=G6+1
G6	G6	=E6+31
G7:G18	G7	=E7+27

 

[EmadMassoud]

Hi EmadMassoud,

You could just use SUMIFS

EmadMassoud.xlsx
	C	D	E	F	G
1			Result
2			6
3
4
5	Block		Start		End
6	1		01-Jul-21		01-Aug-21
7	2		02-Aug-21		29-Aug-21
8	3		30-Aug-21		26-Sep-21
9	4		27-Sep-21		24-Oct-21
10	5		25-Oct-21		21-Nov-21
11	6		22-Nov-21		19-Dec-21
12	7		20-Dec-21		16-Jan-22
13	8		17-Jan-22		13-Feb-22
14	9		14-Feb-22		13-Mar-22
15	10		14-Mar-22		10-Apr-22
16	11		11-Apr-22		08-May-22
17	12		09-May-22		05-Jun-22
18	13		06-Jun-22		03-Jul-22
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIFS($C$6:$C$18,$E$6:$E$18,"<="&TODAY(),$G$6:$G$18,">="&TODAY())
F2	F2	=IF(E2=0,"Today is out of range","")
E7:E18	E7	=G6+1
G6	G6	=E6+31
G7:G18	G7	=E7+27

Hi Toadstool,

Your formula is simple and straightforward

Thank you!

 

[EmadMassoud]

Below are a couple of other options:
My first thought was that since your dates are consecutive, that you could simply use Vlookup with a True (approximate match). This is ruled out by the fact that you need to look up a column to the left.
You can achieve the same thing using a Index/Match with the match type of less than. The only issue might be if the test date is greater than the 03-Jul-22 in the table put together by Toadstool.
It also would not work if there were breaks in the dates ie the start date did not follow on from the previous end date.

The Lookup will handle multiple criteria in any version of Excel.
If you have 365 you would have more options.

20211126 Lookup Between Dates.xlsx
	C	D	E	F	G
1			Toadstool Result	Index Match	Lookup
2			6	6	6
3
4
5	Block		Start		End
6	1		1/07/2021		1/08/2021
7	2		2/08/2021		29/08/2021
8	3		30/08/2021		26/09/2021
9	4		27/09/2021		24/10/2021
10	5		25/10/2021		21/11/2021
11	6		22/11/2021		19/12/2021
12	7		20/12/2021		16/01/2022
13	8		17/01/2022		13/02/2022
14	9		14/02/2022		13/03/2022
15	10		14/03/2022		10/04/2022
16	11		11/04/2022		8/05/2022
17	12		9/05/2022		5/06/2022
18	13		6/06/2022		3/07/2022
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIFS($C$6:$C$18,$E$6:$E$18,"<="&TODAY(),$G$6:$G$18,">="&TODAY())
F2	F2	=INDEX($C$6:$C$18,MATCH(TODAY(),$E$6:$E$18,1),0)
G2	G2	=LOOKUP(2,1/ (($E$6:$E$18<=TODAY())*($G$6:$G$18>=TODAY())),$C$6:$C$18)
E7:E18	E7	=G6+1
G6	G6	=E6+31
G7:G18	G7	=E7+27

Fantastic!

Thanks a lot Alex Blakenburg for the detailed explanation, and for the two additional alternative options!

Personally, I like using INDEX & MATCH combination a lot

Thank you!