Index Match with date range criteria

Attached is an Excel 2010 workbook with multiple worksheets. It will open up to the one I need help with and the column header is highlighted in yellow to show which column has the formula I need help on. The amounts showing are accurate but the formula is having trouble on some of the lines.

Here is an explanaiton of the worksheets included in the file:
Doc List - this lists all the documents associated to the projects listed in the Project List worksheet. It shows whether the version of the document listed is the most current. It shows the type of work involved. It shows the total cost of the doc. There are formulas to show how much of that work is Captial and how much is Expense. (These formulas will be adjusted but are not used for the formula I need help on.) It also shows which document and the date of said document replaces the individual amounts on the current doc. What this means is: each doc will have a list of items and their individual costs. We might receive another document that shows just the increased amount of those same items. Therefore both documents are current docs with the sum of both docs equaling the new line item totals.

Fixed - This worksheet shows those individual line items in the docs. It shows the total amount, tha capital ratio for each item which then calculates the total cap and total exp amount per line item. It shows the date that line was added to the file. There is a formula to show if that line item was replaced by another and the date it was replaced. This is very important. We may accrue part of that line prior to it being replaced by another doc and this will be shown on the next worksheet.

Accruals-Pmts - This sheet is where each month the user will go in and list those line items that are being accrued. The date in Col K is important to show when this transaction was done. The amount in Col N is what I need help with. It should return the amount of the line item at the time the accrual is being done. To explain a little more, if col K shows the date as May-13, the date behind that is 5/1/13. The accruals are done at the end of the month so the formula in N needs to change the date in Col K to endofmonth. Then it needs to look up the amount that was in play as of endofmonth May 2013 on the Fixed worksheet without going over. So if the Fixed sheet shows this item listed twice, once with 5/1/13 in Col U on the fixed sheet and once with 7/7/13, the formula in the Accruals-Pmts sheet should pull the corresponding data associated to the 5/1 date and not the 7/7/13 date.

I really hope this makes sense. If not, please ask specific questions and I will try to be more clear. Thanks.

When an Index Match formula is matching to more than one value, I've always seen the formula written with the 1 in it. As for what this is supposed to do, it needs to return the amount of the item in Col M as of the date in Col K based on what's in the "Fixed" worksheet. If you have ideas to make this work, I welcome them. Thanks so much.

Thanks for your help. Unfortunately, the formula you gave is not finding the subsequent values of the item in Col M. You can see that for rows 4&5 there is no dollar amount and there should be. The formula seems to only be finding the first instance.

Is the Month part of the formula going to find the right value if there was a change twice in one month? Is it taking into account year as well?

For some reason when I put your formula into my main file (not the stipped down one I sent here), the formula comes up with the #VALUE! error. I'm still trying to figure that one out.

The formula needs to find the amount if the end of the month on row 4 and 5 is greater than or equal to the date on the Fixed sheet.
So row 4 has a date of Jun-2013 (which translates to 06/01/2013). The date on the Fixed sheet is 05/15/2013. Therefore as long as the end of the month on the accrual sheet is not greater than the date on the Fixed sheet, the formula should return the amount associated to the 05/15/2013 date. I hope this makes sense. Thanks so much for all your help.

Tsjallie, The formula works perfectly until you drag it to row 9. Here I need it just to find $197,854.43 instead of summing up that number times however many times the match appears. Unfortunately the Sumif formula sums the number in column S however many times it finds the match. Instead I need it to only return the number once.

Let me see if I can explain a bit more. The Fixed sheet lists all of the items (col L) per doc short name (col J). You can see that the full doc name (col I) may vary but the short doc name (col J) combines the similar docs into one consistent name without losing the original name of the original docs. The work performed , accrued, and invoiced will be against whichever revision of the doc that applies so we can't easily say one entire doc replaces another. You can see that doc short name P308-128 had item "EofC" originally listed at a cost of $156,924.42 when it first appeared on the docs. The next doc again listed this item ut now it had a cost of $97,854.43. The third version of the doc had the item and price the same. Now when we first got the doc we accrued a portion of this item at its original cost. The next accrual was still at the original cost as the first revision was not received. The third accrual is now against the revised doc. Therefore the Accrual worksheet Col N needs to show the price of the item at the time it was accrued. I hope that helps clarify what I am trying to accomplish. Thanks so much for your time.

In reading my last post, it appears some of the letters I typed did not appear. Here is a clean version of that text:

Let me see if I can explain a bit more. The Fixed sheet lists all of the items (col L) per doc short name (col J). You can see that the full doc name (col I) may vary but the short doc name (col J) combines the similar docs into one consistent name without losing the original name of the original docs. The work performed, accrued, and invoiced will be against whichever revision of the doc that applies so we can't easily say one entire doc replaces another. You can see that doc short name P308-128 had item "EofC" originally listed at a cost of $156,924.42 when it first appeared on the docs. The next doc again listed this item but now it had a cost of $197,854.43. The third version of the doc had the item and price the same. Now, when we first got the doc we accrued a portion of this item at its original cost. The next accrual was still at the original cost as the first revision doc was not received. The third accrual is now against the revised doc. Therefore the Accrual worksheet Col N needs to show the price of the item at the time it was accrued. I hope that helps clarify what I am trying to accomplish. Thanks so much for your time.

Thank you for the formula. I'm including a new file to show what's actually happening in my file. I don't know if why the formula isn't entirely working is due to other formulas I have in my file or if it's due to something else.

Thanks Tsjallie. I look forward to seeing the formula to your 3rd idea as I'm not a fan of masking issues. Also I have other formulas that caluclate based on the results of this one and don't want to risk those ones having issues. I really truly appreciate all your help on this. I could not do this without you!

Tsjallie, this works perfectly. Thank you so much for your assistance on this!