XLookup on Pivot Table using pivot column headers/names instead of worksheet column letters

Question

I am using xLookup to pull some data out of a pivot table. Currently my formulas look like this:

=XLOOKUP($A2,'RecT'!$A$5:$A$30,'RecT'!$C$5:$C$30,0)

But in my pivot table, column A is called Grade and Column C is called Confirmed. It would be much easier to read the formulas if the column header/name were used to refer to the data.

Is there some version of the formula that will look more like:

=XLOOKUP($A2,PIVOTTABLENAME[[#Values],["Grade"]],PIVOTTABLENAME[[#Values],["Confirmed"]])

(I made up that syntax - I know it doesn't work)

If you use XLOOKUP you can click the data column (excluding header) and you'll see what it's refered as. Kinda similar to what you're asking. — P.b
– P.b, Commented Oct 13, 2022 at 20:51
That does sound like what I am trying to do, but I can't figure out where to click in the pivot table. — Adam
– Adam, Commented Oct 13, 2022 at 21:20

P.b · Accepted Answer · 2022-10-14 17:39:09Z

1

Please see situation below:

If you want to retrieve data meeting conditions of A12 and B12 you could use the following formula in C12:

=GETPIVOTDATA("C",$G$1,"A",A12,"B",B12)

This looks for the values in the Pivot Table in column with values of table header named C from the original table.

$G$1 being the cell the table is stored.

It looks for the values where the values in data column named "A" equals the value in A12 and values in data column named "B" equals the value in B12.

Change it to your needs.

edited Oct 14, 2022 at 17:39

answered Oct 14, 2022 at 9:17

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XLookup on Pivot Table using pivot column headers/names instead of worksheet column letters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related