Vlookup (or match&Index) in Pivot Table

[MartinL]

I have seen questions about this in various places but I cant see an answer to my ordeal.

Am I right in thinking that "PivotTable1" is not a valid table_array name.

My Pivot table by definition is dynamic so I can not fix the range

anyway here is what I am trying to do:

On the left is my pivot table (PivotTable1)
I want to take this info and recreate it on the right adding some additional info about the values

For example Retailer 1 has a total of 303 promotions over a period
divided up between 5 different types of promotion
Money off, for example represents 25.08% of total promotions for that retailer (Retailer1 in this case)

If a retailer has no offers during the selected period, the row will dissapear from pivot table
If a Retailer8 (not shown) has a promotion it will appear in the pivot Table
If no retailer has any BOGOFF promotions that column will dissapaer etc

My attempts so far have revolved around this, to no avail:
In Cell L3

=VLOOKUP(K3,pivottable1,2)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Count of Promotion Type	Column Labels
2	Row Labels	BOGOFF	Extra Free	Link Save	Money Off	Other	Grand Total					BOGOFF	Extra Free	Link Save	Money Off	Other	Grand Total
3	Retailer1			227	76		303				Retailer1			227	76		303
4	Retailer2		15	118	14	24	171					0.00%	0.00%	74.92%	25.08%	0.00%
5	Retailer3	10	4	183	22	1	220
6	Retailer4		6	59	77		142				Retailer2		15	118	14	24	171
7	Retailer5			95	35		130					0.00%	8.77%	69.01%	8.19%	14.04%
8	Retailer6	5	5	225	75	21	331
9	Retailer7		3	123	23		149				Retailer3	10	4	183	22	1	220
10	Grand Total	15	33	1030	322	46	1446					4.55%	1.82%	83.18%	10.00%	0.45%
11
12											Retailer4		6	59	77		142
13												0.00%	4.23%	41.55%	54.23%	0.00%
14
15											Retailer5			95	35		130
16												0.00%	0.00%	73.08%	26.92%	0.00%
17
18											Retailer6	5	5	225	75	21	331
19												1.51%	1.51%	67.98%	22.66%	6.34%
20
21											Retailer7		3	123	23		149
22												0.00%	2.01%	82.55%	15.44%	0.00%
Sheet2

 

[GlennUK]

Just add your item of Count Of Promotion Type again, and have that one have Field Settings of Show Values as % of row. And pull your pivottable items to be the same shape as you require.

 

[GlennUK]

Like so:

Excel Workbook
	D	E	F	G	H	I	J	K
1			Promotion Type
2	Name	Data	BOGOFF	Extra Free	Link Save	Money Off	Other	Grand Total
3	Retailer1	Count		8	6	6	9	29
4		%	0.00%	27.59%	20.69%	20.69%	31.03%	100.00%
5	Retailer2	Count		9	5	7	8	29
6		%	0.00%	31.03%	17.24%	24.14%	27.59%	100.00%
7	Retailer3	Count		4	7	7	11	29
8		%	0.00%	13.79%	24.14%	24.14%	37.93%	100.00%
9	Retailer4	Count		7	3	7	10	27
10		%	0.00%	25.93%	11.11%	25.93%	37.04%	100.00%
11	Retailer5	Count		5	7	6	10	28
12		%	0.00%	17.86%	25.00%	21.43%	35.71%	100.00%
13	Retailer6	Count		3	3	5	6	17
14		%	0.00%	17.65%	17.65%	29.41%	35.29%	100.00%
15	Retailer7	Count		7	6	7	9	29
16		%	0.00%	24.14%	20.69%	24.14%	31.03%	100.00%
17	Total Count			43	37	45	63	188
18	Total %		0.00%	22.87%	19.68%	23.94%	33.51%	100.00%
Sheet3

 

[MartinL]

Thanks Glenn

I cant get mine to look like yours, rather side by side.

Overview

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 92px"><COL style="WIDTH: 148px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"><COL style="WIDTH: 73px"><COL style="WIDTH: 122px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"><COL style="WIDTH: 73px"><COL style="WIDTH: 184px"><COL style="WIDTH: 157px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Date</TD><TD>(Multiple Items)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Sector</TD><TD>Prepared</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD>Column Labels</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD>Count Promotion Type</TD><TD> </TD><TD> </TD><TD> </TD><TD>% Promotion Type</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Total Count Promotion Type</TD><TD style="TEXT-ALIGN: center">Total % Promotion Type</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Row Labels</TD><TD style="TEXT-ALIGN: center">BOGOFF</TD><TD style="TEXT-ALIGN: center">Extra Free</TD><TD style="TEXT-ALIGN: center">Link Save</TD><TD style="TEXT-ALIGN: center">Money Off</TD><TD style="TEXT-ALIGN: center">BOGOFF</TD><TD style="TEXT-ALIGN: center">Extra Free</TD><TD style="TEXT-ALIGN: center">Link Save</TD><TD style="TEXT-ALIGN: center">Money Off</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Ret1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">176</TD><TD style="TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">89.34%</TD><TD style="TEXT-ALIGN: center">10.66%</TD><TD style="TEXT-ALIGN: center">197</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Ret2</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">92</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">3.70%</TD><TD style="TEXT-ALIGN: center">85.19%</TD><TD style="TEXT-ALIGN: center">11.11%</TD><TD style="TEXT-ALIGN: center">108</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Ret3</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">9.76%</TD><TD style="TEXT-ALIGN: center">21.95%</TD><TD style="TEXT-ALIGN: center">68.29%</TD><TD style="TEXT-ALIGN: center">41</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">Ret4</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">73.81%</TD><TD style="TEXT-ALIGN: center">26.19%</TD><TD style="TEXT-ALIGN: center">42</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Ret5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">81</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">5.05%</TD><TD style="TEXT-ALIGN: center">3.03%</TD><TD style="TEXT-ALIGN: center">81.82%</TD><TD style="TEXT-ALIGN: center">10.10%</TD><TD style="TEXT-ALIGN: center">99</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Ret6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">67</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">0.00%</TD><TD style="TEXT-ALIGN: center">97.10%</TD><TD style="TEXT-ALIGN: center">2.90%</TD><TD style="TEXT-ALIGN: center">69</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">Grand Total</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">456</TD><TD style="TEXT-ALIGN: center">84</TD><TD style="TEXT-ALIGN: center">0.90%</TD><TD style="TEXT-ALIGN: center">1.98%</TD><TD style="TEXT-ALIGN: center">82.01%</TD><TD style="TEXT-ALIGN: center">15.11%</TD><TD style="TEXT-ALIGN: center">556</TD><TD style="TEXT-ALIGN: center">100.00%</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[GlennUK]

It's hard to describe, but I pulled the Column Label part into the Row Label area.