Date range matches

[DKDRM]

Hi. Need help with a formula that looks up a specific date in a column, finds the range of dates in which it fits and then prints the year assigned to that range in another column. For example, I'm trying to find the year that corresponds to 10/3/11 (in col L) based on the range of dates. The year would then show up in col M. The range of dates and corresponding years look like this (headings are in row 1):

Col AP Col AQ Col AR

9/1/2008 8/31/2009 2008

9/1/2009 8/31/2010 2009

9/1/2010 8/31/2011 2010

9/1/2011 10/31/2012 2011

11/1/201210/31/2013 2012


Help is greatly appreciated. Saw many similar posts and have tried several ideas, but no luck yet.


Thank you!

 

[Hui]

Dkdrm


Firstly, Welcome to the Chandoo.org forums


It will be something like:

=INDEX(AR2:AR6,SUMPRODUCT((L1>=AP2:AP6)*(L1<=AQ2:AQ6)*ROW(AP2:AP6))-1)

 

[DKDRM]

Thank you, Hui. I also copied this formula over to do the same thing, but with just numbers instead of dates. It returns 1.40 when it should be returning 4.0 except if the value in Col O is "5.__", it returns #VALUE!. My formula is:

=INDEX($C$4:$C$11,SUMPRODUCT((O6>=$A$4:$A$11)*(O6<=$B$4:$B$11)*ROW($C$4:$C$11))-1)

The columns look like:

A B C

0 5 5.00

6 17 4.00

18 29 1.90

30 41 1.40

42 53 1.20


I'm not that familiar with putting these formulas together, so I just try to build them based on other similar ones that have worked. I've checked the basics, so I'm at a loss on why I'm only getting certain and wrong values returned. Maybe I shouldn't be using the SUMPRODUCt formula?


Thank you Again!

When the value in Col O is