Need formula to return a specific value if a specified date is between two other dates

Hello,

I'm trying to write a formula that will look at one reference date, compare it to two named ranges of dates and then return a number that represents the row where the first date is between the other two. Please see the attached example workbook for a better visual explanation.

For example: I have two sheets, one is named "Lists" and contains 3 dynamic named ranges; Lists_BudgetNum - in Column A is a numerical value representing the start and end dates of the row it is on, Lists_BudgetStart - in Column B represents all of the start dates for each budget year, and Lists_BudgetEnd - Column C represents all of the end dates for each budget.

The other sheet is named Transactions and contains the date field (G3) that I want to find to be in between the Lists_BudgetStart and Lists_BudgetEnd ranges. Cell J3 on the Transaction sheet is where the formula will be and where I want to return the budget number associated with the Budget Start and Budget End dates that the reference date in G3 falls between.

If Transactions!J3 date value is 10/11/2013, the Lists_BudgetNum returned should be "3". I think the formula should use INDEX MATCH and COUNTIFS but I haven't been at all successful using it so I tried using an IF statement instead of COUNTIFS which returns "1" no matter what the reference date is currently.... That's what you will see in the example file right now.


Thanks,

If budgetNum is alway number, try:
=SUMPRODUCT((Lists_BudgetStart<=$G3)*(Lists_BudgetEnd>=$G3)*Lists_BudgetNum)
IF it is text, try with INDEX.

Quang, if I could buy you a beer for this, I surely would! That works perfectly. Thank you!

Thank for feedback and for the rept!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.