vlookup pivot table date issue

[Chookz]

Hi guys,
I have the following pivot table:
<table style="width:auto;"><tbody><tr><td></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From Excel</td></tr></tbody></table>

In a seperate table I have a list of dates in the format dd/mm/yy.
I had been using a vlookup formula to extract values from the pivot table based on the dates. The formula I am using is:
=VLOOKUP(TEXT(A21,"d-mmm"),'TL Pivot Table'!C5:D2797,2,0)
where A21 is the date (dd/mm/yy) that i am after.

The problem is my pivot table displays the date as d-mmm (as the date is grouped days, months, years) and now that we are in 2012, when i try to look up the 7th Jan (7-Jan) it gives me the value for 2011, not 2012.
Is anyone able to help me out with either a new formula or pivot table setup options.
Any help is much appreicated
Cheers,
Chookz

 

[Robert Mika]

If your data are for 2011 how do you expect return of 2012?

IS there a part of the table that is not visible on your picture?

 

[Chookz]

There's a big part of the table not visible in the picture. This is just to show what format the table is in to help understand what is going on. The values keep going down and are into Jan 2012 now. So for example, when i want the value for Jan 7 2012, the vlookup is giving me the value for Jan 7 2011

 

[Robert Mika]

You will not get what you want as 2011 is only in one cell and the next down are empty.
Try option Repeat All Item Labels, from the Layout Group on Design Ribbon in PivotTable Tools Option.
Then you need to concatenate your Year and month(there are several option) and then use VLOOKUP.
The other option will be to have VLOOKUP taking data from your raw data.
Can you post sample in a workable format?

You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[Chookz]

Hi Robert, thanks for your reply.
I think using vlookup to get the data from my raw data will be the way to go.
As im not great with vlookup formulas, are you able to suggest a formula for me? Sorry but im unable to download and programs on my work computer as i dont have admin privlages
My 'raw data' looks like this (Headers and first line only, the actual table has thousands of rows):

Worksheet name: Raw Data
A, B, C, D, E, F, G
1 Name, Start Time, Category, TRIMP, CTL, ATL, TSB,
2 John, 07/01/12, Running, 54, 102, 189, -89,

The values I would need are TRIMP, CTL, ATL & TSB.
The previous formula I used for the Pivot table was:

=VLOOKUP(TEXT(A17,"d-mmm"),'TL Pivot Table'!C5:D2797,2,0)
where A17 was the date to lookup and TL Pivot Table was the name of the worksheet. Im assuming the date and data range sections need to be changed, but again im not very good with formulas so any help you can give would be great