Search and Return Rows Based on Monthly Start-End Dates

[stpeter]

Hi All:
A new addition to my recent posts for search and return.
Since I need to keep a monthly inventory of expected shipments, can I have a formula to search the Produce Tab, and return the products that have a start and end date from the first to the last of each month?
I tried to use recent array formulae but they don’t work for dates, unfortunately.
Please note the date format, though the columns have been formatted to date, occasionally the formula returns a number, and I have to manually format the date columns again. So a formula that has the date format would be best.
The workbook is attached.
Merci,
SP

[CAntosh]

Try the array formula below in A2 of your June sheet. Confirm it with Ctrl + Shift + Enter, then fill down through however many rows you think you'll need, then fill right through your columns.

=IFERROR(INDEX(Produce!$A$2:$F$100,SMALL(IF(MONTH(Produce!$D$2:$D$100)=6,ROW(Produce!$D$2:$D$100)-1),ROW(1:1)),COLUMN(A:A)),"")

It's set up for 100 rows on your produce sheet; if you need more, change the "100"s in the formula to something bigger. Be sure to make sure the ranges are all the same size, though. For July, change the "=6" in the middle of the formula to "=7", for August "=8", etc. You can format the date column on your monthly sheets to the format of your choosing. Let me know if you have any difficulties.

[stpeter]

Hi Cantosh,
This is fantastic. I increased the number of rows as needed and it works so well. There is so much I don't even know I don't know!!!
I will close the thread.
SP

[CAntosh]

Glad I could help! Take heart, knowing what you don't know is a sound step toward knowing something new.