Index - Match Formula with Dates

[gsmith923]

I am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.

=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0)

10/17/09 10/24/09 10/31/09
P-10 P-10 P-11

01/31/09 P-01
02/28/09 P-02
04/04/09 P-03
05/02/09 P-04
05/30/09 P-05
07/04/09 P-06
08/01/09 P-07
08/29/09 P-08
10/03/09 P-09
10/31/09 P-10
11/28/09 P-11
01/02/10 P-12

 

[gsmith923]

I am looking to be able to pull data based on a 12 period reporting as projects rotate in different cycles. This would call for a need to have some activities take place in early January and as a result be able to pull data in the same fashion as the other 11 periods.

Thanks!

 

[jeffreybrown]

So per 01/31 would need to equal P-01?

 

[gsmith923]

That is correct. Anything from Jan 1 to Jan 31st would equal P-01.

 

[jeffreybrown]

Okay probably not the best, but the best I can come up with and it works with the given data in post #1. Could this work for you? If not then maybe one of the MVP's can help with a simpler solution.

=IF(A1 < DATE(YEAR(A5),MONTH(A5),DAY(A5)),"P-01",IF(ISNA(VLOOKUP(A$1,$A$5:$B$16,2,0)),INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0),VLOOKUP(A$1,$A$5:$B$16,2,0)))

 

[gsmith923]

This worked! Thanks so much for your help!