Unexplainable results from my vlookup

[Mary-Lou]

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

 

[Peo Sjoblom]

Are you looking for an exact match, if so change the 1 to 0 at the end of
the formula

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Mary-Lou]

No, I'm not looking for an exact match in this case. Worksheet B contains
only date ranges, ie start day of week and end day of week with no dates in
between.

I just don't understand why only 20 records out of 365 come back with
strange results.

 

[Peo Sjoblom]

Maybe the other dates are text and not numbers? Also note that if you use 1
as the option you need to have all number dates in ascending order

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Mary-Lou]

All the dates have the same format and are sorted in ascending. Just
strange. I will probably go with the "roundup" format as this works pretty
good - unless it's a Saturday. So I will just have to ensure nobody starts

 

[Max]

Just some observations ..

Re your orig post's:

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

I'm not sure the above works "as-is". The table_array spans only 2 cols
while the column index is 3 ?? - we'd get #REF! if B2 did contain a value for
the VLOOKUP to evaluate ?

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)

Based on your set-up in "Worksheet B" (from your orig. post) ..
as an alternative .. think we could try, array-entered,
i.e. press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER):
=IF($B2="","",INDEX(WorksheetB!$E$2:$E$53,MATCH(1,($B2>=WorksheetB!$C$2:$C$53)*($B2<=WorksheetB!$D$2:$D$53),0)))
Then copy down as required

See whether the above yields better results ..

 

[Mary-Lou]

Sorry, I just realized I copied in the wrong vlookup. Here is the right one:
=IF($H2="","",VLOOKUP($H2,Sheet1!C$2:E$53,3,1)

 

[Biff]

If you want to send me your file I'll take a look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Max]

Sorry, I just realized I copied in the wrong vlookup. Here is the right one:
=IF($H2="","",VLOOKUP($H2,Sheet1!C$2:E$53,3,1)

Just as an alternative play to see how it goes ..
perhaps you could also try the array formula below (adapted to suit)

Array-enter it as before,
by pressing CTRL+SHIFT+ENTER to confirm the formula
after placing it into the formula bar:

=IF($H2="","",INDEX(Sheet1!$E$2:$E$53,MATCH(1,($H2>=Sheet1!$C$2:$C$53)*($H2<=Sheet1!$D$2:$D$53),0)))

Then copy down as required.

Let me know whether the above yields better results ..

Unlike the VLOOKUP which doesn't make use of the end dates in col D, the
above array INDEX / MATCH does. A subtle difference .. <g>

 

[Mary-Lou]

Excellent! This works great. Thank you very very much.

 

[Max]

Excellent! This works great. Thank you very very much.

Great to hear it worked !
Thanks for the feedback