Date Range Formula Help Needed

[BrettOlbrys1]

Good morning, I am looking for some help with a formula. I want to be able to return a date value if the date itself is between a set of dates. In my spreadsheet, I have:

Lookup Date	Answer	blank column	Start Date Range	End Date Range	Value to Return
1/1/21	?		1/1/21	1/31/21	1/1/21
1/2/21	?		2/1/21	2/28/21	2/1/21
1/3/21	?		3/1/21	3/31/21	3/1/21
1/4/21	?		4/1/21	4/30/21	4/1/21

- column A (Lookup Date) has dates that start from 1/1/21 thru 12/31/21 (above dates are just an example)
- column B (Answer) is where I want my returned value to appear
- column C is blank to keep data separate
- column D is the start of the date range for that month
- column E is the end of the date range for that month
- column F is the date value I want returned by looking up the value in column A by using the date ranges in columns E and F

The values in column A go from 1/1/21 thru 12/31/21 and my date ranges also have all 12 months too. I cannot figure out the formula to do this and any help is appreciated.

Thanks

Brett

 

[Fluff]

Why should the 2nd of Jan return a value of 1st of Feb?

 

[jasonb75]

Your question and table don't seem to match each other very well. It appears that you simply want to return the date from the 'Start Date Range' that the date in column A falls into, which appears to be nothing more than the 1st day of the month in question. If that is the case then you don't need such a table to look it up, there are a number of much easier ways to do it.

 

[BrettOlbrys1]

Why should the 2nd of Jan return a value of 1st of Feb?

Don't consider the data as being aligned in the rows as illustrated. Columns A & B could be on sheet 1 and the table of lookup values (columns D, E, F) could be on sheet 2, or 3, or anywhere. I just placed them like this for ease of my different data sets.

 

[Fluff]

That does not answer my question, you have shown that 1/2/21 should return 2/1/21. Why?

 

[BrettOlbrys1]

Actually, I think I figured it out. I just need to do a LOOKUP formula on my table (I don't even need columns E and F), such as: =LOOKUP(A1,$D$1:$D$27,$D$1:$D$27) and then drag my formula down the column.

 

[Fluff]

Glad you've sorted it & thanks for the feedback.
However if all you start dates are the 1st of the month, as shown, you can just use

=EOMONTH(A2,-1)+1

 

[BrettOlbrys1]

Glad you've sorted it & thanks for the feedback.
However if all you start dates are the 1st of the month, as shown, you can just use

=EOMONTH(A2,-1)+1

That's even better, thank you!!!

 

[Fluff]

My pleasure.