On a vlookup, I am getting strange results on only 5% of the values.
My "Worksheet A" houses info about staff and contains some headings like:
- column A = "Resource Name"
- column H = "Resource Start Date"
- column I = "Week # Resource Started""

My "Worksheet B" houses my workplace's fiscal calendar (fiscal year runs
mid-Oct to mid-Oct, not Jan 1 to Dec 31) and contains the following headings:
- column C = "Start Date of Week"
- column D = "End Date of Week"
- column E = "Week #" (52 rows)

In column I (in Worksheet A), I have the following vlookup:
=IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1) )
The purpose is to lookup the resource start date (column H in Worksheet A)
and compare it against the date range in Worksheet B (columns C, D, E) and
return the week# value (I need the week number for another calculation).

The function worked great on random testing but when I did a thorough test I
found it only worked on 95% of the records - every entry from Dec 18 thru to
Jan 6 all return a value of "12" for the week # (that's 20 days stating the
answer is week 12).

How can this be? What am I doing wrong? And how can I fix it?

Example of data in Worksheet B:
Start date of week=22/10/05, End date of week=28/10/05, Week#= 1
Start date of wee=29/10/05, End date of week=04/11/05, Week#= 2
.....
Start date of week=17/12/05, End date of week=23/12/05, Week#= 9
Start date of week=24/12/05, End date of week=30/12/05, Week #= 10
.....
Start date of week=14/10/06, End date of week =20/10/06, Week #=52