Extract Pivot Table Row Label (not value)

Question

In Excel 2010, I have a pivot table, in compact form, with 3 Row Labels (that represent a management hierarchy). Which of the 3 management levels is displayed in a particular row will change from day to day. (The source data is on another spreadsheet with the fields Manager L3, Manager L2 and Manager L1 in columns with John Smith, Gary Glen and Bob Stevens under them, respectively.)

In the column next to the pivot table, I need to capture which row label is being shown.

Pivot Table example

Is there a formula I can use to produce that info?

Let me know if you need more information. Thank you in advance.

I'm going to go ahead and guess that you can't do that, unless you create a list of all the Manager L3s, Manager L2s and Manager L1s on another sheet, then use a formula to figure out what category they fall into. — dwirony
See, that's why I never use the compact form. It just sucks, period. With Tabular form, you get to see who is in what category just by looking at the column headers. Any reason why you don't just switch to that? — jeffreyweir
Because I'm going to have a new column of data for each day running across and I need to conserve space as much as possible. I've thought of an alternative though...create helper columns that combine the name of the individual with their level (L1, L2, L3), then use that in the pivot table. It's enough to allow me to build the driving formula for the daily columns. — Ruthie09

dwirony dwirony · Accepted Answer · 2017-11-09T22:33:29

Like I said above, I believe your solution would be to create an array on another (hidden) sheet like:

Then run a VLOOKUP to it: =VLOOKUP(A45,Sheet1!$A$1:$B$6,2,FALSE)

You'd put that formula in cell B45 on your sheet and drag down.

Extract Pivot Table Row Label (not value)

1 Answers