Formula to find lookupvalue between two separated date

[WashingtonDC74]

Hi,

I'm facing to an issue.

Indeed, I would like to find the weeknumber rounds up a date.

I have 2 columns which represent each starting date/ending date of one week.

For instance, I would like to find as output "2020/03" as of this input "17-1-2020 (January 17th, 2020)

I tried all of lookup formula (XLOOKUP,VLOOKUP,HLOOKUP,INDEX,MATCH) but no successful

I looking forward your ideas regarding my topic!

BR

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1
2	2020/01	01/01/2020	07/01/2020
3	2020/02	08/01/2020	14/01/2020		17/01/2020
4	2020/03	15/01/2020	21/01/2020		2020/03
5	2020/04	22/01/2020	28/01/2020
6	2020/05	29/01/2020	04/02/2020
7	2020/06	05/02/2020	11/02/2020
8	2020/07	12/02/2020	18/02/2020
9	2020/08	19/02/2020	25/02/2020
10	2020/09	26/02/2020	03/03/2020
11	2020/10	04/03/2020	10/03/2020
12
Lookup

Cell Formulas
Range	Formula
E4	E4	=XLOOKUP(E3,B2:B11,A2:A11,"",-1)

 

[Toadstool]

Hi WashingtonDC74,

Try:

Book1
	A	B	C	D	E	F
1	Week	Start Date	End Date		Input=	17-Jan-20
2	2020/01	01-Jan-20	07-Jan-20		Output=	2020/03
3	2020/02	08-Jan-20	14-Jan-20
4	2020/03	15-Jan-20	21-Jan-20
5	2020/04	22-Jan-20	28-Jan-20
6	2020/05	29-Jan-20	04-Feb-20
7	2020/06	05-Feb-20	11-Feb-20
8	2020/07	12-Feb-20	18-Feb-20
9	2020/08	19-Feb-20	25-Feb-20
10	2020/09	26-Feb-20	03-Mar-20
11	2020/10	04-Mar-20	10-Mar-20
Sheet1

Cell Formulas
Range	Formula
F2	F2	=INDEX($A$2:$A$11,MATCH(1,INDEX(($F$1>=$B$2:$B$11)*($F$1<=$C$2:$C$11),,),0))

 

[WashingtonDC74]

Awesome! Many thanks!
I dont be boring but you can quicly summarize why you chosen -1 as match model ? Why you didnt pickup 1 as match model instead of?

 

[Toadstool]

It is 1 and not -1.
It's looking for a logical 1 when checking $F$1>=$B$2:$B$11 and $F$1<=$C$2:$C$11

EDIT: Oops! I didn't see @Fluff had already posted an alternative.

 

[WashingtonDC74]

It is 1 and not -1.
It's looking for a logical 1 when checking $F$1>=$B$2:$B$11 and $F$1<=$C$2:$C$11

Sorry my reply was destined to Fluff...

By the way, many thanks for your help!
I tried to mix Index and match formulas but I didnt multiply $F$1<=$C$2:$C$11...
That's make logical now

Thanks!

BR

 

[Fluff]

The -1 tells the formula to find "an exact match or next smaller" so as it cannot find 17th Jan it gets the next smaller value which is 15th Jan.