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

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)

CodePudding user response：

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.