FIND DATE IN A RANGE

[drluke]

I have a range with dates in B2:C26. I want my formula to find the row range that my reference date in F3 falls into and then
(a) if my reference date is smaller than the start date in the range, return value in col A of the row immediately above it, or
(b) if my reference date is greater than the end date in the range, return value in col A of the row immediately below it, or
(c) if my reference date is greater than or equal to the start date in the range but smaller than or equal to the end date in the range (i.e within the row range) then return value in col A of the same row
In my example the value in G4 should be 08/2021

I found a lot of information about doing each of the actions above on an "individual" basis, but nothing that will find the reference date and do the other actions in one formula. Any help or advice would be appreciated.

Macro FAR2 (version 1).xlsb.xlsm
	A	B	C	D	E	F	G
1	Per numb	Start Date	End Date
2	12/2020	24-Nov-20	27-Dec-20
3	1/2021	28-Dec-20	24-Jan-21			Ref Date	Value
4	2/2021	25-Jan-21	21-Feb-21			19/08/2021
5	3/2021	22-Feb-21	28-Mar-21
6	4/2021	29-Mar-21	25-Apr-21
7	5/2021	26-Apr-21	23-May-21
8	6/2021	24-May-21	27-Jun-21
9	7/2021	28-Jun-21	25-Jul-21
10	8/2021	26-Jul-21	22-Aug-21
11	9/2021	23-Aug-21	26-Sep-21
12	10/2021	27-Sep-21	24-Oct-21
13	11/2021	25-Oct-21	21-Nov-21
14	12/2021	22-Nov-21	26-Dec-21
15	1/2022	27-Dec-21	23-Jan-22
16	2/2022	24-Jan-22	21-Feb-22
17	3/2022	20-Feb-22	27-Mar-22
18	4/2022	28-Mar-22	24-Apr-22
19	5/2022	25-Apr-22	22-May-22
20	6/2022	23-May-22	26-Jun-22
21	7/2022	27-Jun-22	24-Jul-22
22	8/2022	25-Jul-22	21-Aug-22
23	9/2010	22-Aug-22	25-Sep-10
24	10/2022	26-Sep-22	23-Oct-22
25	11/2022	24-Oct-22	20-Nov-22
26	12/2022	21-Nov-22	25-Dec-22
27	1/2023	26-Dec-22	23-Jan-23
28	2/2023	24-Jan-23	20-Feb-23
29
Sheet1

Cell Formulas
Range	Formula
F4	F4	=TODAY()

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(A2:A28,MATCH(F4,B2:B28,1))

 

[hrayani]

I found it searching on the net... see if this helps

=LOOKUP(2,1/($B$2:$B$28<=F4)/($C$2:$C$28>=F4),$A$2:$A$28)

 

[drluke]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(A2:A28,MATCH(F4,B2:B28,1))

Thank you! Works perfectly.

 

[drluke]

I found it searching on the net... see if this helps

=LOOKUP(2,1/($B$2:$B$28<=F4)/($C$2:$C$28>=F4),$A$2:$A$28)

Thank you - this also works.

 

[hrayani]

Thank you - this also works.

Pleasure

 

[Fluff]

Glad to help & thanks for the feedback.