Strange vlookup results

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.

WorksheetB!B$2:C$53,3??

--
Don Guillett
SalesAid Software
[email protected]
"Mary-Lou" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

a) I forgot to mention I enter the vlookup formula in column D in Workbook A.
b) I also realized for a heading in Worksheet B I entered "Week $" instead
of "Week #".
c) Also, I notice the columns in my example have lost their alignment which
makes the example harder to read

For the vlookup statement: =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))

B2 = Column B in Worksheet A (Start date of resource)
WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column C
(end date of week)
3 = the week # (this is the value I'm trying to return)
1 = true

Hope this helps.


"Don Guillett" wrote:

> WorksheetB!B$2:C$53,3??
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mary-Lou" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

Not sure how to fix your VLOOKUP, but why not just do:

=ROUNDUP(($B2-StartDate)/7,0)

where "StartDate" is a named cell, or if you don't want to use named
ranges try:

=ROUNDUP(($B2-WorksheetB!$A$1)/7,0)

in this example the start date would be in cell A1 of WorksheetB

I suggest you take a look at the help index for vlookup. In order to look
over 3 columns you will need to have three columns to look in.

--
Don Guillett
SalesAid Software
[email protected]
"Mary-Lou" <[email protected]> wrote in message
news:[email protected]...
> a) I forgot to mention I enter the vlookup formula in column D in Workbook
> A.
> b) I also realized for a heading in Worksheet B I entered "Week $" instead
> of "Week #".
> c) Also, I notice the columns in my example have lost their alignment
> which
> makes the example harder to read
>
> For the vlookup statement:
> =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
>
> B2 = Column B in Worksheet A (Start date of resource)
> WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column
> C
> (end date of week)
> 3 = the week # (this is the value I'm trying to return)
> 1 = true
>
> Hope this helps.
>
>
> "Don Guillett" wrote:
>
>> WorksheetB!B$2:C$53,3??
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Mary-Lou" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>>

There is 3 columns: Start Date of Week (Saturday), End Date of Week
(Friday), Week # (identifies if it's 1 thru 52)


"Don Guillett" wrote:

> I suggest you take a look at the help index for vlookup. In order to look
> over 3 columns you will need to have three columns to look in.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mary-Lou" <[email protected]> wrote in message
> news:[email protected]...
> > a) I forgot to mention I enter the vlookup formula in column D in Workbook
> > A.
> > b) I also realized for a heading in Worksheet B I entered "Week $" instead
> > of "Week #".
> > c) Also, I notice the columns in my example have lost their alignment
> > which
> > makes the example harder to read
> >
> > For the vlookup statement:
> > =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
> >
> > B2 = Column B in Worksheet A (Start date of resource)
> > WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and column
> > C
> > (end date of week)
> > 3 = the week # (this is the value I'm trying to return)
> > 1 = true
> >
> > Hope this helps.
> >
> >
> > "Don Guillett" wrote:
> >
> >> WorksheetB!B$2:C$53,3??
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "Mary-Lou" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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'm not trying to round up a calendar week date - I'm using a different range
based on my company's fiscal year of November to October.

The vlookup function resides in Worksheet A - therefore B2 is actually the
Start Date of the resource in Worksheet A.

a) I need to take the Start Date of the Resource from Worksheet A and
compare it against the calendar date range in Worksheet B
b) Worksheet B contains the 'fiscal calendar' breakdown by week (start date
of week, end date of week, week #). This fiscal calendar goes from November
to October ... and not January to December
c) I then need to find which week number the resource started on (because I
need the week number for another calculation)


"[email protected]" wrote:

> Not sure how to fix your VLOOKUP, but why not just do:
>
> =ROUNDUP(($B2-StartDate)/7,0)
>
> where "StartDate" is a named cell, or if you don't want to use named
> ranges try:
>
> =ROUNDUP(($B2-WorksheetB!$A$1)/7,0)
>
> in this example the start date would be in cell A1 of WorksheetB
>
>

I think "StartDate" means "FiscalYearStartDate", in which case the
formula barbetta suggested would calculate the week of the fiscal year
based on the fiscal year start date. It finds the number of days
between the fiscal year start date *you provide* and the date in column
B of worksheetA, then divides by 7 and rounds up to the nearest integer
to give you the correct week based on your fiscal year. Maybe I'm not
understanding this correctly, but using that formula is much simpler
than the VLOOKUP you're trying to do.

It appears the fiscal year start date is currently at WorksheetB!$A$2,
so

=IF($B2="","",ROUNDUP(($B2-WorksheetB!$A$2)/7,0))

should do it. Let me know if I misunderstand.

Thank you.

I tried this out, but whenever a resource start date (in Worksheet A)
matched exactly to the Week Start Date in Worksheet B, the value did not
round up and always remained 1 less than it should have been.

When testing out all 365 days of the year, I received 52 incorrect values.
For example, the first recource start date is Oct 22 and when compared
against the Start Week Date (also Oct 22), the value came back as zero...but
the next 6 values worked out fine, then the 7th would be incorrect, with the
next 6 values incorrect, etc.


"[email protected]" wrote:

> I think "StartDate" means "FiscalYearStartDate", in which case the
> formula barbetta suggested would calculate the week of the fiscal year
> based on the fiscal year start date. It finds the number of days
> between the fiscal year start date *you provide* and the date in column
> B of worksheetA, then divides by 7 and rounds up to the nearest integer
> to give you the correct week based on your fiscal year. Maybe I'm not
> understanding this correctly, but using that formula is much simpler
> than the VLOOKUP you're trying to do.
>
> It appears the fiscal year start date is currently at WorksheetB!$A$2,
> so
>
> =IF($B2="","",ROUNDUP(($B2-WorksheetB!$A$2)/7,0))
>
> should do it. Let me know if I misunderstand.
>
>

Your vlookup formula, AS SHOWN, will give a "REF!" everytime. Look at the
formula again>read my last post > think about it.

--
Don Guillett
SalesAid Software
[email protected]
"Mary-Lou" <[email protected]> wrote in message
news:[email protected]...
> There is 3 columns: Start Date of Week (Saturday), End Date of Week
> (Friday), Week # (identifies if it's 1 thru 52)
>
>
> "Don Guillett" wrote:
>
>> I suggest you take a look at the help index for vlookup. In order to look
>> over 3 columns you will need to have three columns to look in.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Mary-Lou" <[email protected]> wrote in message
>> news:[email protected]...
>> > a) I forgot to mention I enter the vlookup formula in column D in
>> > Workbook
>> > A.
>> > b) I also realized for a heading in Worksheet B I entered "Week $"
>> > instead
>> > of "Week #".
>> > c) Also, I notice the columns in my example have lost their alignment
>> > which
>> > makes the example harder to read
>> >
>> > For the vlookup statement:
>> > =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
>> >
>> > B2 = Column B in Worksheet A (Start date of resource)
>> > WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and
>> > column
>> > C
>> > (end date of week)
>> > 3 = the week # (this is the value I'm trying to return)
>> > 1 = true
>> >
>> > Hope this helps.
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> WorksheetB!B$2:C$53,3??
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> [email protected]
>> >> "Mary-Lou" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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'm a little confused as I'm not getting REF# at all... I get values for
every record, and out of 365 records, only 20 are showing me a strange value
that does not make any sense.

"Don Guillett" wrote:

> Your vlookup formula, AS SHOWN, will give a "REF!" everytime. Look at the
> formula again>read my last post > think about it.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mary-Lou" <[email protected]> wrote in message
> news:[email protected]...
> > There is 3 columns: Start Date of Week (Saturday), End Date of Week
> > (Friday), Week # (identifies if it's 1 thru 52)
> >
> >
> > "Don Guillett" wrote:
> >
> >> I suggest you take a look at the help index for vlookup. In order to look
> >> over 3 columns you will need to have three columns to look in.
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "Mary-Lou" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > a) I forgot to mention I enter the vlookup formula in column D in
> >> > Workbook
> >> > A.
> >> > b) I also realized for a heading in Worksheet B I entered "Week $"
> >> > instead
> >> > of "Week #".
> >> > c) Also, I notice the columns in my example have lost their alignment
> >> > which
> >> > makes the example harder to read
> >> >
> >> > For the vlookup statement:
> >> > =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
> >> >
> >> > B2 = Column B in Worksheet A (Start date of resource)
> >> > WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and
> >> > column
> >> > C
> >> > (end date of week)
> >> > 3 = the week # (this is the value I'm trying to return)
> >> > 1 = true
> >> >
> >> > Hope this helps.
> >> >
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> WorksheetB!B$2:C$53,3??
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> [email protected]
> >> >> "Mary-Lou" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

(b,c) is TWO columns. If you are trying to lookup over 3 columns you need
b2:d53 (b,c,d)
VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))

--
Don Guillett
SalesAid Software
[email protected]
"Mary-Lou" <[email protected]> wrote in message
news:[email protected]...
> I'm a little confused as I'm not getting REF# at all... I get values for
> every record, and out of 365 records, only 20 are showing me a strange
> value
> that does not make any sense.
>
> "Don Guillett" wrote:
>
>> Your vlookup formula, AS SHOWN, will give a "REF!" everytime. Look at the
>> formula again>read my last post > think about it.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Mary-Lou" <[email protected]> wrote in message
>> news:[email protected]...
>> > There is 3 columns: Start Date of Week (Saturday), End Date of Week
>> > (Friday), Week # (identifies if it's 1 thru 52)
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> I suggest you take a look at the help index for vlookup. In order to
>> >> look
>> >> over 3 columns you will need to have three columns to look in.
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> [email protected]
>> >> "Mary-Lou" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > a) I forgot to mention I enter the vlookup formula in column D in
>> >> > Workbook
>> >> > A.
>> >> > b) I also realized for a heading in Worksheet B I entered "Week $"
>> >> > instead
>> >> > of "Week #".
>> >> > c) Also, I notice the columns in my example have lost their
>> >> > alignment
>> >> > which
>> >> > makes the example harder to read
>> >> >
>> >> > For the vlookup statement:
>> >> > =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
>> >> >
>> >> > B2 = Column B in Worksheet A (Start date of resource)
>> >> > WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and
>> >> > column
>> >> > C
>> >> > (end date of week)
>> >> > 3 = the week # (this is the value I'm trying to return)
>> >> > 1 = true
>> >> >
>> >> > Hope this helps.
>> >> >
>> >> >
>> >> > "Don Guillett" wrote:
>> >> >
>> >> >> WorksheetB!B$2:C$53,3??
>> >> >>
>> >> >> --
>> >> >> Don Guillett
>> >> >> SalesAid Software
>> >> >> [email protected]
>> >> >> "Mary-Lou" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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 see now what you are talking about - which just showed me how badly I
really messed up on the example I posted (as my vlookup really really does
include 3 columns). Let me try this again:

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 #"
This worksheet has a row for every week in the year (52).

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 week=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

In column I (in Worksheet A), I have the following vlookup:
=IF($H2="","",VLOOKUP($H2,Sheet1!C$2:E$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).

Hope this makes more sense.


"Don Guillett" wrote:

> (b,c) is TWO columns. If you are trying to lookup over 3 columns you need
> b2:d53 (b,c,d)
> VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mary-Lou" <[email protected]> wrote in message
> news:[email protected]...
> > I'm a little confused as I'm not getting REF# at all... I get values for
> > every record, and out of 365 records, only 20 are showing me a strange
> > value
> > that does not make any sense.
> >
> > "Don Guillett" wrote:
> >
> >> Your vlookup formula, AS SHOWN, will give a "REF!" everytime. Look at the
> >> formula again>read my last post > think about it.
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "Mary-Lou" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > There is 3 columns: Start Date of Week (Saturday), End Date of Week
> >> > (Friday), Week # (identifies if it's 1 thru 52)
> >> >
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> I suggest you take a look at the help index for vlookup. In order to
> >> >> look
> >> >> over 3 columns you will need to have three columns to look in.
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> [email protected]
> >> >> "Mary-Lou" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > a) I forgot to mention I enter the vlookup formula in column D in
> >> >> > Workbook
> >> >> > A.
> >> >> > b) I also realized for a heading in Worksheet B I entered "Week $"
> >> >> > instead
> >> >> > of "Week #".
> >> >> > c) Also, I notice the columns in my example have lost their
> >> >> > alignment
> >> >> > which
> >> >> > makes the example harder to read
> >> >> >
> >> >> > For the vlookup statement:
> >> >> > =IF($B2="","",VLOOKUP($B2,WorksheetB!B$2:C$53,3,1))
> >> >> >
> >> >> > B2 = Column B in Worksheet A (Start date of resource)
> >> >> > WorksheetB!B$2:C$53 = Worksheet B column B (Start date of week) and
> >> >> > column
> >> >> > C
> >> >> > (end date of week)
> >> >> > 3 = the week # (this is the value I'm trying to return)
> >> >> > 1 = true
> >> >> >
> >> >> > Hope this helps.
> >> >> >
> >> >> >
> >> >> > "Don Guillett" wrote:
> >> >> >
> >> >> >> WorksheetB!B$2:C$53,3??
> >> >> >>
> >> >> >> --
> >> >> >> Don Guillett
> >> >> >> SalesAid Software
> >> >> >> [email protected]
> >> >> >> "Mary-Lou" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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.
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

This will work correctly if you change the "Range Lookup" value from 1
to "TRUE". It worked for me here.

I tried this (with fingers crossed) but still received the exact same results
where the 20 records from Dec 18 to Jan 6 still incorrectly return a value of
week 12. I'm going to try redoing everything from scratch and see what
happens.

"Waxaholic" wrote:

> This will work correctly if you change the "Range Lookup" value from 1
> to "TRUE". It worked for me here.
>
>

Mary-Lou,
If the data that you initially posted is the exact data you are using, then
you need to correct that first. You have the wrong year for these dates
show - 04 instead of 05 and 05 instead of 06.
31-Dec-04 6-Jan-05 11
7-Jan-05 13-Jan-05 12

NickHK

"Mary-Lou" <[email protected]> wrote in message
news:[email protected]...
> I tried this (with fingers crossed) but still received the exact same
results
> where the 20 records from Dec 18 to Jan 6 still incorrectly return a value
of
> week 12. I'm going to try redoing everything from scratch and see what
> happens.
>
> "Waxaholic" wrote:
>
> > This will work correctly if you change the "Range Lookup" value from 1
> > to "TRUE". It worked for me here.
> >
> >

This modified one should work:

=ROUNDUP(($B2+1-StartDate)/7,0)

I think the reason you are not getting the vlookup you want is that the
above satisfies your requirement and is so much simpler. Maybe someone
will come along and post the vlookup/index/match/offset formula you are
requesting (and if they do it will probably be an array formula), but
the above formula is simple and does what you are looking for.

Yep, that was the problem. I did finally notice that awhile back after I just
about gave up. The data is imported in from another group - which I guess
they did not test first. Thanks.

"NickHK" wrote:

> Mary-Lou,
> If the data that you initially posted is the exact data you are using, then
> you need to correct that first. You have the wrong year for these dates
> show - 04 instead of 05 and 05 instead of 06.
> 31-Dec-04 6-Jan-05 11
> 7-Jan-05 13-Jan-05 12
>
> NickHK
>
> "Mary-Lou" <[email protected]> wrote in message
> news:[email protected]...
> > I tried this (with fingers crossed) but still received the exact same
> results
> > where the 20 records from Dec 18 to Jan 6 still incorrectly return a value
> of
> > week 12. I'm going to try redoing everything from scratch and see what
> > happens.
> >
> > "Waxaholic" wrote:
> >
> > > This will work correctly if you change the "Range Lookup" value from 1
> > > to "TRUE". It worked for me here.
> > >
> > >
>
>
>