1

I have some raw data as shown below. Raw Data

I created a Pivot Table for it, mainly just for formatting purposes (the ability to nicely group and expand the sub-items). The Pivot Table is shown below. Pivot Table

I need to display the LastUsageTime for each of the items (UsageName) listed in the Pivot Table. It is simply a lookup of the LastUsageTime column from the Raw Data.

From my research so far, it looks like I have to use Index+Match to do the lookup to match all 3 columns (TPC, TeamProjectName, UsageName). Something like: 

{=INDEX('Raw Data'!A2:E2000,MATCH(1,('Raw Data'!A2:A2000=???????)*('Raw Data'!B2:B2000=??????)*('Raw Data'!C2:C2000=A6),0),5)}

However, I don't know what to use to retrieve the TPC and TeamProjectName of each UsageName in the Pivot Table.

I hope this makes sense, and thanks for any help!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
AngieM
  • 735
  • 6
  • 27
  • 1
    Great idea! It doesn't understand the time format I currently have in the LastUsageTime column, but I can fix that. Thanks for the suggestion! – AngieM Oct 10 '14 at 20:20

1 Answers1

1

For the sake of an answer:

Process your LastUsedTime data into what is recognised by Excel as Date/Time and then add a field to your PivotTable for Σ VALUES Max of LastUsageTime (or Max of ... whatever the column of adjusted LastUsageTime data is labelled).

pnuts
  • 58,317
  • 11
  • 87
  • 139