Find each date in the current month and return the corresponding data

[flarunner]

I am trying to find each date in the current month from a column of dates and return the corresponding data for each date in that month.

On Sheet1:

Column B is the list of dates for the year.

Column E is the data I would like returned.

On Sheet2:

Column B is the list of dates for the current month, excluding Sundays.

Column D is where I would like to put a formula to get the data from Column E of Sheet1.

Cell I5: Dynamic cell containing the formula: TODAY()

Cell I3: Dynamic cell referencing Cell I5 and the helper column K4:K15


I have used the following formula (as an array), which lists the data in Sheet1, Column E:

IFERROR(INDEX(Sheet1!$E$2:$E$367,SMALL((IF(LEN(Sheet1!$E$2:$E$367),ROW(INDIRECT("1:"&ROWS(Sheet1!$E$2:$E$367))))),ROW(A1)),1),"")

BUT, it doesn’t search out the data for each month.

Since the three cells in Column I of Sheet2 will change each month, I need a formula that will get that data as the months change.


Thank you in advance for helping me.

[jason.b75]

If you enter this in I3 of sheet2, then you can do away with the lookup table on the right.

=EOMONTH(I5,-1)+1

To answer the question you asked, enter this one in D2 and fill down.

=IFERROR(VLOOKUP(B2,Sheet1!$B$2:$E$367,4,0),"")

edit:-

You will need this one in B2 as well..

=B2+1+(WEEKDAY(B2)=7)

Otherwise you will get problems if the first day of the month is a Sunday. (try March 2015 if you want to test the theory).

[flarunner]

Thank you jason. All three formulas worked wonderfully.

Marking the thread as solved.

Thanks again for your help.

~fla