I'm trying to look up values that are between two dates. In columns B-D I have my first list of data and then in columns F-H I have my second list. In column M is my dates for every Friday through the next year and a half. The results I am looking for are in row 6 in columns N-P.

Example: The Friday day in cell M6 is 9/14/12 so I need to find the order in either the first list (Columns B-D) or the second list (Columns F-H) that either equals 9/14/12 or is less than 9/14/12 but greater than 9/7/12 (Cell M5). When it finds that order I need the TOTAL LBS of that order put into column N, the DUE DATE put into column O, and the ORDER # put into column P. I need the two different lists for other reasons so I cannot make them into 1 list.

There is a catch to this. There could be more than 1 order due within that same week so I would need to combine them into 1 row in columns N-P but use the later of the 2 due dates and total the LBS and use a "/" to split the order numbers.

Sample.xlsx
I have hardcoded the Orders ranges for now during testing. These can be changed to methods that find the extend of data.

Open in new window