GETPIVOTREPORT Formula or Understanding

[Ba6a6is2]

So I am trying to understand the GETPIVOTREPORT formula in excel. I am trying to run some formulas (Rank, IndexMatch, Sumifs...ect) off of a pivot report and wanted to know what the best way to do so without having to redo formulas once I change the filter in the report. Is this something a GETPIVOTREPORT would help with or are there other ways? Thanks!

 

[Jerry Sullivan]

Hi @Ba6a6is2,

I'm assuming that you are referring to the GETPIVOTDATA formula function (unless your Excel for Mac uses GETPIVOTREPORT).

In general terms, GETPIVOTDATA allows you to return a data value from a PivotTable by specifying one or more PivotField+Item pairs.
GETPIVOTDATA formulas can be very useful in doing lookups by using cell references for some of the parameters in lieu of constant values.

GETPIVOTDATA functions can be used in array formulas to return arrays of values although they are more commonly used to return single data values displayed in the PivotTable report.

The tasks you describe could possibly be done using GETPIVOTDATA functions in array formulas- it's hard to say for sure without a specific example.
Another alternative would be to use Index-Match formulas. Those can allow some degree of dynamic lookup of a Pivot given some assumptions or constraints.