Return two most recent values prior to specified date?

[tgallag1]

A1:<Enter> Date
A2:<Enter> Location Number
A3:<Return> Score 1
A4:<Return> Score 2


Data:

Column B: Location Numbers
Column C: Dates
Column D: Scores

If I enter a date in A1 and a location number in A2, I need a formula to look through the data in columns B, C and D and return the two most recent scores prior to the date entered. Data are continuously added to columns B, C, and D, so the the number of data rows increases throughout the year.

For example if I enter 6/29/2011 in A1 and enter location number 234 in A2, I need corresponding scores returned for the two dates closest to, but not later than 6/29/2011. So if the data contain scores for dates of 3/15/2011, 5/17/2011 and 7/2/2011 for location 234, I need the scores for 3/15 and 5/17 returned.

Thanks,

[NBVC]

Something like:

For largest...

=INDEX(D4:D11,MATCH(1,(B4:B11=A2)*(C4:C11=LARGE(IF(B4:B11=A2,IF(C4:C11<A1,C4:C11)),1)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

and for second largest

=INDEX(D4:D11,MATCH(1,(B4:B11=A2)*(C4:C11=LARGE(IF(B4:B11=A2,IF(C4:C11<A1,C4:C11)),2)),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

adjust ranges to suit, but don't use overly large ranges....

If needed, you can use dynamic named ranges.

[dilipandey]

Hi tgallag1,

Consider below scenario:- you have the data arrangement like this where data starts from column A and spreads till column D

40723 Location Numbers Dates Scores
234 234 40617 1
234 40680 2
234 40726 3
250 40616 4
290 40727 5

Two get the two dates as per your criteria, use following formula in A3 and A4 respectively:-

{=VLOOKUP(MAX(IF($C$2:$C$6<(MAX(IF(($C$2:$C$6<$A$1)*($B$2:$B$6=$A$2),$C$2:$C$6,""))*($B$2:$B$6=$A$2)),$C$2:$C$6,"")),C:D,2,0)}

and


{=VLOOKUP(MAX(IF(($C$2:$C$6<$A$1)*($B$2:$B$6=$A$2),$C$2:$C$6,"")),C:D,2,0)}

Feel free to get back in case you need further explanation.



Regards,
DILIPandey
< Click on below star if this helps >