How do I vlookup a date to match a date range (period)?

[gkisystems]

On my first spreadsheet within a workbook, I have some data that looks like this:


A B C D E F G...
Year Period Week Ended MiscData1 MiscData2 MiscData3 Etc
2008 2/9/2008 abc efg hij klm
2008 3/15/2008 abc efg hij klm
2009 5/16/2009 abc efg hij klm
2010 9/18/2010 abc efg hij klm


On another spreadsheet within the same workbook, I have my fiscal period calendar that looks like this:


A B C D E
Year Month Beginning End Period
2008 02 2/1/2008 3/1/2008 2008.02
2008 03 3/2/2008 3/29/2008 2008.03
2009 05 4/26/2009 5/30/2009 2009.05
2010 09 9/5/2010 10/02/2010 2010.09


As you can see, my fiscal calendar is NOT a normal calendar month. For example, October 2nd 2010 is considered to be September 2010 - not October.


On the first spreadsheet in column B (Period), how do I use a VLOOKUP formula (or something similar) to go look at my accounting calendar Beginning & End Dates to see what period the date in column C (Week Ended) should fall into?

 

[AlphaFrog]

This is on one sheet for illustration...

<br />

Book1
	A	B	C	D	E	F	G
1	Year	Period	Week Ended	MiscData1	MiscData2	MiscData3	Etc
2	2008	2008.02	2/9/2008	abc	efg	hij	klm
3	2008	2008.03	3/15/2008	abc	efg	hij	klm
4	2009	2009.05	5/16/2009	abc	efg	hij	klm
5	2010	2010.09	9/18/2010	abc	efg	hij	klm
6
7	Year	Month	Beginning	End	Period
8	2008	02	2/1/2008	3/1/2008	2008.02
9	2008	03	3/2/2008	3/29/2008	2008.03
10	2009	05	4/26/2009	5/30/2009	2009.05
11	2010	09	9/5/2010	10/2/2010	2010.09
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX($E$8:$E$11,MATCH(1,(C2>=$C$8:$C$11)*(C2<=$D$8:$D$11),0))}
B3	{=INDEX($E$8:$E$11,MATCH(1,(C3>=$C$8:$C$11)*(C3<=$D$8:$D$11),0))}
B4	{=INDEX($E$8:$E$11,MATCH(1,(C4>=$C$8:$C$11)*(C4<=$D$8:$D$11),0))}
B5	{=INDEX($E$8:$E$11,MATCH(1,(C5>=$C$8:$C$11)*(C5<=$D$8:$D$11),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gkisystems]

This formula actually works in theory, but not in practice because I have 100,000+ rows of data and it is not able to handle the calculation/processing. Is there a simpler formula that would work?

Yours:
={INDEX('Accounting Calendar'!$E:$E,MATCH(1,(C3>='Accounting Calendar'!$C:$C)*(C3<='Accounting Calendar'!$D:$D),0))}

Potential Alternative?
=VLOOKUP(C2,'Accounting Calendar'!$C:$E,3,TRUE)

In the alternative formula, I'm just doing a lookup at the beginning date...but I'm not sure if this is an iron clad way of doing it.

Also - how did you paste in the image of the spreadsheet to this forum? I would like to know - that's pretty cool!

 

[T. Valko]

This formula actually works in theory, but not in practice because I have 100,000+ rows of data and it is not able to handle the calculation/processing. Is there a simpler formula that would work?

Yours:
={INDEX('Accounting Calendar'!$E:$E,MATCH(1,(C3>='Accounting Calendar'!$C:$C)*(C3<='Accounting Calendar'!$D:$D),0))}

Potential Alternative?
=VLOOKUP(C2,'Accounting Calendar'!$C:$E,3,TRUE)

In the alternative formula, I'm just doing a lookup at the beginning date...but I'm not sure if this is an iron clad way of doing it.

Also - how did you paste in the image of the spreadsheet to this forum? I would like to know - that's pretty cool!

Don't use the entire columns as range references, use the specific range.

Still array entered.

=INDEX('Accounting Calendar'!$E$2:$E$100000,MATCH(1,IF(C3>='Accounting Calendar'!$C$2:$C$100000,IF(C3<='Accounting Calendar'!$D$2:$D$100000,1)),0))

The nested IF is a bit more efficient compared to using array multiplication when calculating over large ranges.

 

[sanrv1f]

If your period ranges do not overlap each other and there is no gap between the end of a period and beginning of the next, then a simple VLOOKUP would always give the the correct result