Excel GETPIVOTDATA formula - how to drag the formu

问题:

I need to create a formula that takes data from pivot table and the data is automatically update when I drag the formula across in my table (picture 2).

My pivot table looks as below:

My table is as below:

My GETPIVOTDATA formula is as below:

=GETPIVOTDATA("Is it valid?",'Overall by Site-2'!$A$8,"Is it valid?","Valid","Week commencing",DATE(2019,1,14))

I tried to add "Week commencing",BK8-DATE(YEAR(BK8),1,0)+1) to my code but it doesn't pick any data.

Thanks in advance.

回答1:

Instead of GETPIVOTDATA, just use either VLOOKUP or INDEX/MATCH formulas:

=VLOOKUP(BK8,'Overall by Site-2'!$A:$C,3,0)

or

=INDEX('Overall by Site-2'!$C:$C,MATCH(BK8,'Overall by Site-2'!$A:$A,0))

I personally prefer to use INDEX/MATCH as it is much faster method and also much more efficient, especially if you work with large data sets.