I have been trying to come up with a formula to lookup a value in one worksheet to put into another worksheet based on a date range from a date provided in the target worksheet.
I have two streams of data on different worksheets. In worksheet 1, I have in columns B through D: item description, Period, Cost. In Worksheet 2, I have in columns B through E: Item Description, From Date, To Date, Cost.
I am trying to find the cost, to put in Column D in Worksheet 1, found in column E in worksheet 2 for each "item description" (in column B) that matches to the "item description" in Worksheet 2 (column B), where the Period (in column C, Worksheet 1) is on or after the From Date (column C, Worksheet 2) AND on or before the To Date (column D, Worksheet 2).
I have attached an example worksheet for reference. I am confused if I should use VLOOKUP or SUMPRODUCT functions (or some other function for that matter).
Thanks for your help.
Larry
(UPDATED 3/15/12 17:11, WITH CORRECTED SPREADSHEET ATTACHED)
Bookmarks