M
Mary-Lou
On a vlookup, I am getting strange results on only 5% of the values.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006
I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
…. …. ….
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
…. …. ….
14-Oct-06 20-Oct-06 52
I am using the following vlookup that takes the Start Date in worksheet A
and compares it against the calendar range in worksheet B to find out which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
To test this out, I created a similar Worksheet as A that contains an entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6 all
point to week 12. Strange.
Can anyone help me figure this out.
I have a Worksheet A containing hundreds of resources showing:
A B C D
Resource Name Start Date etc etc
Joe 3/16/2006
Mary 4/5/2006
I have a Worksheet B containing a fiscal calendar containing 52 weeks:
A B C
Start Date of Week End Date of Week Week $
22-Oct-05 28-Oct-05 1
29-Oct-05 4-Nov-05 2
5-Nov-05 11-Nov-05 3
12-Nov-05 18-Nov-05 4
…. …. ….
17-Dec-05 23-Dec-05 9
24-Dec-05 30-Dec-05 10
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12
…. …. ….
14-Oct-06 20-Oct-06 52
I am using the following vlookup that takes the Start Date in worksheet A
and compares it against the calendar range in worksheet B to find out which
week it falls into within the fiscal year.
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
To test this out, I created a similar Worksheet as A that contains an entry
for every day of the fiscal year - 365 entries.
95% of the results are fine - but every entry from Dec 18 thru to Jan 6 all
point to week 12. Strange.
Can anyone help me figure this out.