Collate (lookup) Multiple orders at the same time, based on DATE range in a table

Trying to index, match, copy over, all the Sales order information based on the SCHD DATE of my Table(for current week), for each PRODUCT AREA.

Bottom line, I want to be able to list all the current orders for the week 4-10 NOV 2013, by product area. I can't mess with the table because it is giving other VLOOKUP info to another table in it's current form.

So, I believe there should be 2 criteria for each item I want to look for. #1 being the date/s I need to filter for, and then the product area I want to find this week's orders for, with the third piece being the info (cell) I am looking for.
Formula is in Cell A4 of ORDER STATUS TAB.

INDEX('Open Orders'!$G$2:$G$1389, SMALL(IF($B$1='Open Orders'!F2:F500, ROW('Open Orders'!$G$2:$G$1389)-MIN(ROW('Open Orders'!$G$2:$G$1389))+1, ""), ROW(A1)))

I can't get this formula to give me all the orders that are open this week, by product area?? Please help, will greatly improve my daily efficiency in tracking open orders in one spot instead of using unreliable PIVOT TABLES.
Thanks.