Hi all,

I'm having a problem doing a relatively complex query on a pivot table. I think it may have to do with how I'm setting up my table or perhaps I'm using the wrong method to reference the pivot table.

What I've got in my reference table are days (1 years worth) as my column headers. In my row labels I have provinces and that's further broken down into deactivation reason codes.

I'm trying to query on this pivot table because it will be updated on a weekly basis. This information will be automatically populated into another worksheet that summarizes this info and does other calculations.

I need to summarize this information by province but still also show the deactivation reason codes.

I'm attempting to use an Index with 2 match functions embedded in it. That way I can search for the day and the province and just copy that formula across my summary table. My problem comes in when I do my match on the province, since I need to show the deactivation reason code I don't know how I can tell excel to just look in a certain subset of rows that follows the matched province. The size of the subset of rows will vary by province.

I was hoping to be able to split my 2 row labels so that it will look like this

Province Deactivation Reason Code

ON ABC
ON BCD
ON CDE
AB ABC
AB FGH

Instead of it's current setup of

ON
ABC
BCD
CDE
AB
ABC
FGH

Does anyone have any solutions?

Thank you very much