Formula to Look up values from a pivot table

[Ecel Dumbo]

Hello,


Could you please suggest a formula to look up values from a pivot table without altering the structure of the pivot table ? PLease refer below attachment


If u suggest SUMPRODUCT, I would like to mention the pivot table keeps growing month by month, and as it gets bigger, it takes lot of time for excel to derive values and sometimes it stalls the workbook


Thanks


http://speedy.sh/kkyk3/Look-UP-Data.xls

 

[vasim]

You can use =GETPIVOTDATA()


In the cell type = and select any value in pivot, it will show something like....I selected E12


=GETPIVOTDATA("Amount",'Pivot table Look UP Data'!$A$7,"Territory Manager","CAMERON WHEELER","Period",3,"Mapping","Healon GV")


No change the "Territory Manager", "Mapping" & "Month" field into range, something like


Example:-

=GETPIVOTDATA("Amount",'Pivot table Look UP Data'!$A$7,"Territory Manager",A1,"Period",D1,"Mapping",A4)


Your D1 needs to be 3 in above example....

 

[Ecel Dumbo]

Thanks Indian. It works for one cell. When I drag the formula , the correct values are not showing up. Please advise.

 

[nazmul_muneer]

Dear Ecel Dumbo

In the sheet Sheet1, B1=1, C1=2, D1=3

In B2, type/copy the formula

=GETPIVOTDATA("Amount",'Pivot table Look UP Data'!$A$7,"Territory Manager",$A$1,"Period",B$1,"Mapping",$A2)


Now copy the cell and paste B2:D4


Regards


Muneer

 

[roygargi]

Hie! Am vry poor in vlookup..... pls help me

 

[bobhc]

roygargi


It goes against good manners to hijack some one else,s post. You have a question that is different from this post, start your own post with a clear title to attract the correct response from forum members.

 

[Ecel Dumbo]

Thanks Nazmul and Indian for your valuable time