Pivot Table as Source Range

J

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
 
J

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.
 
J

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
 
J

Jim May

although the formula:

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

Works great!!!
Thanks
 
Top