Vlookup and Pivot Tables

[colinheslop1984]

I have a pivot table of data which gets updated daily, some products that might appear one day as they are new, some products may disappear once sold out. Its a regular changing data set.

Within this data set are unit cost prices. I want to use a best seller list I get from a different report, lookup the items I have sold to get their cost price for me to then work out GM.

Vlookup doesn't work with a pivot table.

Are there any workarounds anyone can suggest?

 

[mrshl9898]

You can add the data sets to PowerPivot, create a relationship and put in a new Pivot.

Another option is do the lookup next to the pivot and run a macro on pivot refresh to tidy it up (add or remove rows) as the size of the table changes.