Pivot Table as Source Range

[Jim May]

I just tried using a Pivot table in a Vlookup() and it doesn't seem to work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA

 

[Jim Thomlinson]

Since a pivot table does not repeat the heading values down the columns you
are not going to have a lot of luck using the pivot table as the source for a
vlookup. Perhaps a better explanation of what you are trying to do would
help. A sumproduct formula directed against the source data for the pivot
table might be a better option.

 

[Jim May]

Jim - I have on Sheet1 a Pivot table in Range A3:B20

Sum of Contracts $$
State Total
PA 227,112 '=Vlookup(A5, Sheet2!A5:B20, 2) hoped to bring in
227,817
FL 150,000
Mo 125,000

On sheet2 I have a Pivot table in Range A3:B20
Sum of Contracts $$
State Total
PA 227,817
FL 86,662
etc




FA

 

[Jim May]

although the formula:

=SUMPRODUCT(('S-PT'!$A$5:$A$24=PT!A5)*('S-PT'!$B$5:$B$24))

Works great!!!
Thanks

 

[Roger Govier]

Hi Jim

Take a look at the GetPivotData function.
More help can be found at Debra Dalgleish's site
http://www.contextures.com/xlPivot06.html