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, Oct 13 '22 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, Oct 13 '22 at 21:20

P.b · Accepted Answer · 2022-10-14T17:39:09.463

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.

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

1 Answers1