How do I lookup data in a pivot table

Topic

New Reply

Is there a way to do this? I have a sheet that has items matching the column data in the pivot but I cannot seem to get lookup or vlookup to work.

Reply | Quote

Replies

Hi

If the layout of your Pivot Table is likely to alter, then you should use GETPIVOTDATA.

If the layout of the PT is not likely to alter then there is no reason why VLOOKUP shouldn’t work, or you could use INDEX / MATCH

The attached file shows a small set of data, a simple PT and a Lookup sheet with each of the above methods.

Regards
Roger Govier
Microsoft Excel MVP

Reply | Quote

Thanks! I’ll give them all a try. Not sure why my tests didn’t work last night.

Reply | Quote

HI

I had a similar problem and used Get Pivot

=(GETPIVOTDATA(“movment”,TB_Pivot,”Master_Sub_Account”,TEXT(B34,”#####”)))

At the end I added Text(Lookupvalue,”####”) to Convert my list as I had account numbers 5500,5600 and I wanted to look up the corresponding values from the Pivot table.

Hope this helps

Reply | Quote