Formula needed to return specific value from a pivot table

[saraapple]

I am really struggling with this and hope someone can help me.
I have a pivot table looking at the percentage share of a variable and I need to bring the specific value (as a %) into another excel tab.
Here is my pivot:

13.05.2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K
4	Sum of RPE	Column Labels
5	Week	1	2	3	4	5	6	7	8	9	10
6	10	0.51%	0.37%	0.41%	0.43%	0.36%	0.38%	0.35%	0.36%	0.35%	0.41%
7	Midlands
8	Monday	0.13%	0.10%	0.14%	0.11%	0.08%	0.20%	0.06%	0.09%	0.00%	0.05%
9	Tuesday	0.15%	0.12%	0.10%	0.11%	0.09%	0.07%	0.10%	0.10%	0.05%	0.19%
10	Wednesday	0.10%	0.04%	0.06%	0.04%	0.03%	0.03%	0.05%	0.04%	0.10%	0.08%
11	Thursday	0.07%	0.05%	0.06%	0.17%	0.08%	0.04%	0.08%	0.05%	0.06%	0.00%
12	Friday	0.05%	0.06%	0.06%	0.00%	0.07%	0.04%	0.07%	0.08%	0.06%	0.09%
13	Saturday	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.09%	0.00%
% Split Deliveries

I need my formula to look at the Store (A6) the week number, and then the day of the week.

Here is the table I am trying to input the formula in:

13.05.2020.xlsx
	A	B	C	D	E
1	RPE:	Depot:	North	Total RPE:	1000
2	30	Week:	1		Select Week number
3		Day of week:	Monday		Select Day of Week
4		Region	Midlands		Select Region
6	Store ID	Store Name	Sum of RPE	RPE %	Region
7	10	Birmingham	11.383		Midlands
8	650	Burton	10.674		Midlands
9	305	Cannock TAIL LIFT	7.883		Midlands
10	815	Cheltenham	5.964		Midlands
11	790	Crewe	5.963		Midlands
12	655	Derby New	12.26		Midlands
13	920	Erdington	13.048		Midlands
14	560	Fenton	15.384		Midlands
15	810	Gloucester	8.424		Midlands
16	775	Hereford	5.968		Midlands
17	610	Ilkeston	12.216		Midlands
18	685	Kidderminster - NDS	15.716		Midlands
19	205	Loughborough TAIL LIFT	2.419		Midlands
20	360	Oldbury tail lift	7.843		Midlands
21	55	Redditch TAIL LIFT	11.675		Midlands
22	160	Rugby TAIL LIFT	2.752		Midlands
23	80	Shrewsbury Sundorne	5.797		Midlands
24	35	Solihull TAIL LIFT	5.34		Midlands
25	545	Stafford TAIL LIFT	13.426		Midlands
26	495	Telford NDS	9.637		Midlands
27	510	Walsall	10.549		Midlands
28	590	Wolverhampton	18.428		Midlands
29	935	Worcester	6.337		Midlands
30		Grand Total	219.086
Forecaster - Deliveries

Cell Formulas
Range	Formula
A6:A29	A6	=VLOOKUP(B6,Deliveries!E:F,2,FALSE)
E7:E29	E7	=VLOOKUP(A7,'Stores split by region'!A:D,4,FALSE)

So I want my formula in cell D7 and I want it to look at C1 & C2 and then A7 so it would return the result 0.13%

I am sure this can be done with an index and match formula but for the life of me I cant get it to work.
any help greatly appreciated.

Thank you

 

[Tricepsratops]

Although I am not that good at Excel I'm trying to help but I think I need more information, I know how to do the Index-Match for the Week and Day but no idea about arranging it to look at the specific Store ID "10".

So I would like to know how are the different stores arranged in the worksheet, if its one under the other in different rows, or in different sheets... Could you upload the excel file? Only a portion of it with a couple stores not the whole thing, so I could see how the thing is arranged.

 

[yky]

I think this is what your want. Deliveries is the first sheet you showed above. In the formula, C2 is 1, the week number; C3 is Monday. This formula returns 0.0013.

=INDEX(Deliveries!B8:K13,MATCH(C3,Deliveries!A8:A13,FALSE),MATCH(C2,Deliveries!B5:K5,FALSE))

 

[saraapple]

Thank you for your input, it does work but I have omitted a second lookup I need - could this be added the formula?
In the second sheet I need it to select a store number which will give the rows of information to search in for the day and week.
Below is the first sheet again but this time there is additional information (in total there are over 80 stores so the lists continues further):

13.05.2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	Sum of RPE	Column Labels
5	Week	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
6	10	0.51%	0.37%	0.41%	0.43%	0.36%	0.38%	0.35%	0.36%	0.35%	0.41%	0.41%	0.42%	0.40%	0.48%	0.38%
7	Monday	0.13%	0.10%	0.14%	0.11%	0.08%	0.20%	0.06%	0.09%	0.00%	0.05%	0.06%	0.08%	0.12%	0.09%	0.06%
8	Tuesday	0.15%	0.12%	0.10%	0.11%	0.09%	0.07%	0.10%	0.10%	0.05%	0.19%	0.10%	0.11%	0.09%	0.13%	0.09%
9	Wednesday	0.10%	0.04%	0.06%	0.04%	0.03%	0.03%	0.05%	0.04%	0.10%	0.08%	0.05%	0.04%	0.05%	0.04%	0.05%
10	Thursday	0.07%	0.05%	0.06%	0.17%	0.08%	0.04%	0.08%	0.05%	0.06%	0.00%	0.07%	0.09%	0.07%	0.06%	0.09%
11	Friday	0.05%	0.06%	0.06%	0.00%	0.07%	0.04%	0.07%	0.08%	0.06%	0.09%	0.12%	0.11%	0.08%	0.15%	0.09%
12	Saturday	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.09%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
13	25	0.53%	0.49%	0.49%	0.56%	0.33%	0.54%	0.48%	0.40%	0.44%	0.46%	0.44%	0.54%	0.50%	0.45%	0.42%
14	Monday	0.02%	0.08%	0.03%	0.05%	0.06%	0.29%	0.05%	0.05%	0.00%	0.09%	0.07%	0.06%	0.08%	0.04%	0.05%
15	Tuesday	0.13%	0.12%	0.13%	0.11%	0.07%	0.05%	0.10%	0.10%	0.05%	0.12%	0.06%	0.07%	0.09%	0.13%	0.08%
16	Wednesday	0.12%	0.08%	0.08%	0.14%	0.04%	0.05%	0.08%	0.05%	0.08%	0.12%	0.07%	0.10%	0.10%	0.06%	0.08%
17	Thursday	0.18%	0.11%	0.14%	0.25%	0.05%	0.09%	0.11%	0.11%	0.09%	0.00%	0.13%	0.18%	0.13%	0.09%	0.12%
18	Friday	0.08%	0.11%	0.10%	0.00%	0.11%	0.06%	0.13%	0.09%	0.11%	0.13%	0.12%	0.13%	0.10%	0.12%	0.09%
19	Saturday	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.11%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
20	35	0.58%	0.48%	0.46%	0.45%	0.55%	0.47%	0.49%	0.51%	0.48%	0.58%	0.50%	0.50%	0.53%	0.61%	0.54%
21	Monday	0.06%	0.08%	0.10%	0.06%	0.09%	0.23%	0.08%	0.09%	0.00%	0.13%	0.10%	0.10%	0.11%	0.10%	0.11%
22	Tuesday	0.15%	0.15%	0.12%	0.11%	0.16%	0.10%	0.14%	0.13%	0.09%	0.19%	0.12%	0.13%	0.16%	0.18%	0.11%
23	Wednesday	0.22%	0.11%	0.14%	0.15%	0.12%	0.06%	0.11%	0.11%	0.12%	0.17%	0.13%	0.12%	0.10%	0.15%	0.12%
24	Thursday	0.09%	0.06%	0.05%	0.13%	0.07%	0.04%	0.07%	0.08%	0.12%	0.00%	0.06%	0.08%	0.07%	0.07%	0.11%
25	Friday	0.05%	0.08%	0.07%	0.00%	0.10%	0.05%	0.09%	0.10%	0.07%	0.09%	0.10%	0.07%	0.09%	0.10%	0.09%
26	Saturday	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.08%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
27	45	0.49%	0.48%	0.39%	0.50%	0.45%	0.28%	0.39%	0.46%	0.47%	0.51%	0.53%	0.41%	0.47%	0.41%	0.33%
28	Monday	0.04%	0.05%	0.03%	0.07%	0.09%	0.11%	0.04%	0.06%	0.00%	0.10%	0.10%	0.09%	0.10%	0.03%	0.05%
29	Tuesday	0.16%	0.21%	0.15%	0.16%	0.13%	0.07%	0.14%	0.19%	0.10%	0.17%	0.15%	0.09%	0.16%	0.16%	0.08%
30	Wednesday	0.16%	0.11%	0.12%	0.13%	0.10%	0.05%	0.08%	0.09%	0.15%	0.16%	0.12%	0.12%	0.10%	0.08%	0.10%
31	Thursday	0.09%	0.05%	0.04%	0.14%	0.05%	0.02%	0.07%	0.04%	0.10%	0.00%	0.04%	0.06%	0.06%	0.05%	0.04%
32	Friday	0.04%	0.06%	0.06%	0.00%	0.09%	0.03%	0.06%	0.08%	0.05%	0.08%	0.12%	0.06%	0.05%	0.09%	0.06%
33	Saturday	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.07%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
Deliveries

So I need the formula to match the store number in Col A in sheet one and two, then look at the matches for week and day - can this be done?
Thank you for your time
Sara

 

[yky]

Try this:

=INDEX(Deliveries!$B$7:$P$33,MATCH($C$3,INDIRECT("'Deliveries'!A"&MATCH($C$5,Deliveries!$A$1:$A$34,FALSE) +1& ":A" & MATCH($C$5,Deliveries!$A$1:$A$34,FALSE) +6))+MATCH($C$5,Deliveries!$A$1:$A$34,FALSE)-6,MATCH($C$2,Deliveries!$B$5:$P$5,FALSE))

I put the store number at C5.

Hope somebody can shorten this formula.

 

[saraapple]

Thank you for your help again but I am struggling to make it work. The first store number I need to check against is in A7 but when I change your C5 to A7 I get an #N/A - any ideas?

 

[Tricepsratops]

Lets see if I understand, you need to input the formula in the worksheet "Forecaster-Deliveries", "RPE% Column". And you want it to look at the cells C3 (monday), C2 (number of week) and A7 (store number). And then retrieve from the "Deliveries worksheet" the data.

So for Tuesday, Week2, Store 10 ,the result must be: 0.12% or 0.115858619533159% not rounded up.

If thats it, its simple. The key is you need to find the Row of the store in the "Deliveries" worksheet, then add that number to the row section of the INDEX formula.

Look at this example for Tuesday week 2. The data you uploaded only has 2 stores "10" and "35" thats why there are so many #N/D errors, cause it cant find the rest of the stores.

Traductor de Formulas.xlsm
	A	B	C	D	E	F	G	H
1	RPE:	Depot:	North	Total RPE:	1000
2	30	Week:	2		Select Week number
3		Day of week:	Tuesday		Select Day of Week
4		Region	Midlands		Select Region
5
6	Store ID	Store Name	Sum of RPE	RPE %	Region		Row of store in Deliveries	RPE%
7	10	Birmingham	11.383	0.12%	Midlands		1	0.12%
8	650	Burton	10.674	#N/D	Midlands		#N/D	#N/D
9	305	Cannock TAIL LIFT	7.883	#N/D	Midlands		#N/D	#N/D
10	815	Cheltenham	5.964	#N/D	Midlands		#N/D	#N/D
11	790	Crewe	5.963	#N/D	Midlands		#N/D	#N/D
12	655	Derby New	12.26	#N/D	Midlands		#N/D	#N/D
13	920	Erdington	13.048	#N/D	Midlands		#N/D	#N/D
14	560	Fenton	15.384	#N/D	Midlands		#N/D	#N/D
15	810	Gloucester	8.424	#N/D	Midlands		#N/D	#N/D
16	775	Hereford	5.968	#N/D	Midlands		#N/D	#N/D
17	610	Ilkeston	12.216	#N/D	Midlands		#N/D	#N/D
18	685	Kidderminster - NDS	15.716	#N/D	Midlands		#N/D	#N/D
19	205	Loughborough TAIL LIFT	2.419	#N/D	Midlands		#N/D	#N/D
20	360	Oldbury tail lift	7.843	#N/D	Midlands		#N/D	#N/D
21	55	Redditch TAIL LIFT	11.675	#N/D	Midlands		#N/D	#N/D
22	160	Rugby TAIL LIFT	2.752	#N/D	Midlands		#N/D	#N/D
23	80	Shrewsbury Sundorne	5.797	#N/D	Midlands		#N/D	#N/D
24	35	Solihull TAIL LIFT	5.34	0.15%	Midlands		15	0.15%
25	545	Stafford TAIL LIFT	13.426	#N/D	Midlands		#N/D	#N/D
26	495	Telford NDS	9.637	#N/D	Midlands		#N/D	#N/D
27	510	Walsall	10.549	#N/D	Midlands		#N/D	#N/D
28	590	Wolverhampton	18.428	#N/D	Midlands		#N/D	#N/D
29	935	Worcester	6.337	#N/D	Midlands		#N/D	#N/D
30		Grand Total	219.086	#N/D			#N/D	#N/D
Hoja3

Cell Formulas
Range	Formula
G7:G30	G7	=MATCH(A7,Deliveries!$A$6:$A$33,0)
H7:H30	H7	=INDEX(Deliveries!$B$6:$P$33,MATCH($C$3,Deliveries!$A$7:$A$12,FALSE)+G7,MATCH($C$2,Deliveries!$B$5:$P$5,FALSE))
D7:D30	D7	=INDEX(Deliveries!$B$6:$P$33,MATCH($C$3,Deliveries!$A$7:$A$12,FALSE)+MATCH(A7,Deliveries!$A$6:$A$33,0),MATCH($C$2,Deliveries!$B$5:$P$5,FALSE))

Lets look at the new formulas.
Column G "Row of store in deliveries" finds the row of the store "10" in the deliveries Workbook.
Column H "RPE%" adds the number from column G to the Index formula.
Column D is basically the same as Column H but the formula from column G is inside of it so you can delete Columns G and H.

Bear in mind that the formula is only the size of the partial data you posted here. For all the stores to work you have to increase the size of it, so instead of
=INDEX(Deliveries!$B$6:$P$33 ..... it would be something like =INDEX(Deliveries!$B$6:$P$600

I hope this helps you a bit.

 

[Tricepsratops]

Try this:

=INDEX(Deliveries!$B$7:$P$33,MATCH($C$3,INDIRECT("'Deliveries'!A"&MATCH($C$5,Deliveries!$A$1:$A$34,FALSE) +1& ":A" & MATCH($C$5,Deliveries!$A$1:$A$34,FALSE) +6))+MATCH($C$5,Deliveries!$A$1:$A$34,FALSE)-6,MATCH($C$2,Deliveries!$B$5:$P$5,FALSE))

I put the store number at C5.

Hope somebody can shorten this formula.

This formula actually works as well, at least for me, changing C5 for A7...A29. Very interesting use of Indirect, I've never known how to really make use of it.

 

[Fluff]

Maybe
=INDEX(Deliveries!$B$3:$P$30,MATCH(A7,Deliveries!$A$3:$A$30,0)+MATCH(LEFT($C$3,3),{"Mon","Tue","wed","thu","fri","sat"},0),$C$2)

 

[saraapple]

That helps a lot.....it is perfect!
Thank you so much for breaking it down @Tricepsratops there is no point in me doing this if I do not understand where I am going wrong.
Many thanks to all for your valuable input