Formula needed to return specific value from a pivot table

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
165
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJK
4Sum of RPEColumn Labels
5Week12345678910
6100.51%0.37%0.41%0.43%0.36%0.38%0.35%0.36%0.35%0.41%
7Midlands
8Monday0.13%0.10%0.14%0.11%0.08%0.20%0.06%0.09%0.00%0.05%
9Tuesday0.15%0.12%0.10%0.11%0.09%0.07%0.10%0.10%0.05%0.19%
10Wednesday0.10%0.04%0.06%0.04%0.03%0.03%0.05%0.04%0.10%0.08%
11Thursday0.07%0.05%0.06%0.17%0.08%0.04%0.08%0.05%0.06%0.00%
12Friday0.05%0.06%0.06%0.00%0.07%0.04%0.07%0.08%0.06%0.09%
13Saturday0.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
ABCDE
1RPE:Depot:NorthTotal RPE:1000
230Week:1Select Week number
3Day of week:MondaySelect Day of Week
4RegionMidlands Select Region
6Store IDStore NameSum of RPERPE %Region
710Birmingham11.383Midlands
8650Burton10.674Midlands
9305Cannock TAIL LIFT7.883Midlands
10815Cheltenham5.964Midlands
11790Crewe5.963Midlands
12655Derby New12.26Midlands
13920Erdington13.048Midlands
14560Fenton15.384Midlands
15810Gloucester8.424Midlands
16775Hereford5.968Midlands
17610Ilkeston12.216Midlands
18685Kidderminster - NDS15.716Midlands
19205Loughborough TAIL LIFT2.419Midlands
20360Oldbury tail lift7.843Midlands
2155Redditch TAIL LIFT11.675Midlands
22160Rugby TAIL LIFT2.752Midlands
2380Shrewsbury Sundorne5.797Midlands
2435Solihull TAIL LIFT5.34Midlands
25545Stafford TAIL LIFT13.426Midlands
26495Telford NDS9.637Midlands
27510Walsall10.549Midlands
28590Wolverhampton18.428Midlands
29935Worcester6.337Midlands
30Grand Total219.086
Forecaster - Deliveries
Cell Formulas
RangeFormula
A6:A29A6=VLOOKUP(B6,Deliveries!E:F,2,FALSE)
E7:E29E7=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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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))
 
Upvote 0
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
ABCDEFGHIJKLMNOP
4Sum of RPEColumn Labels
5Week123456789101112131415
6100.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%
7Monday0.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%
8Tuesday0.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%
9Wednesday0.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%
10Thursday0.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%
11Friday0.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%
12Saturday0.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%
13250.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%
14Monday0.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%
15Tuesday0.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%
16Wednesday0.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%
17Thursday0.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%
18Friday0.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%
19Saturday0.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%
20350.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%
21Monday0.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%
22Tuesday0.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%
23Wednesday0.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%
24Thursday0.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%
25Friday0.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%
26Saturday0.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%
27450.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%
28Monday0.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%
29Tuesday0.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%
30Wednesday0.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%
31Thursday0.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%
32Friday0.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%
33Saturday0.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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFGH
1RPE:Depot:NorthTotal RPE:1000
230Week:2Select Week number
3Day of week:TuesdaySelect Day of Week
4RegionMidlands Select Region
5
6Store IDStore NameSum of RPERPE %RegionRow of store in DeliveriesRPE%
710Birmingham11.3830.12%Midlands 10.12%
8650Burton10.674#N/DMidlands #N/D#N/D
9305Cannock TAIL LIFT7.883#N/DMidlands #N/D#N/D
10815Cheltenham5.964#N/DMidlands #N/D#N/D
11790Crewe5.963#N/DMidlands #N/D#N/D
12655Derby New12.26#N/DMidlands #N/D#N/D
13920Erdington13.048#N/DMidlands #N/D#N/D
14560Fenton15.384#N/DMidlands #N/D#N/D
15810Gloucester8.424#N/DMidlands #N/D#N/D
16775Hereford5.968#N/DMidlands #N/D#N/D
17610Ilkeston12.216#N/DMidlands #N/D#N/D
18685Kidderminster - NDS15.716#N/DMidlands #N/D#N/D
19205Loughborough TAIL LIFT2.419#N/DMidlands #N/D#N/D
20360Oldbury tail lift7.843#N/DMidlands #N/D#N/D
2155Redditch TAIL LIFT11.675#N/DMidlands #N/D#N/D
22160Rugby TAIL LIFT2.752#N/DMidlands #N/D#N/D
2380Shrewsbury Sundorne5.797#N/DMidlands #N/D#N/D
2435Solihull TAIL LIFT5.340.15%Midlands 150.15%
25545Stafford TAIL LIFT13.426#N/DMidlands #N/D#N/D
26495Telford NDS9.637#N/DMidlands #N/D#N/D
27510Walsall10.549#N/DMidlands #N/D#N/D
28590Wolverhampton18.428#N/DMidlands #N/D#N/D
29935Worcester6.337#N/DMidlands #N/D#N/D
30Grand Total219.086#N/D#N/D#N/D
Hoja3
Cell Formulas
RangeFormula
G7:G30G7=MATCH(A7,Deliveries!$A$6:$A$33,0)
H7:H30H7=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:D30D7=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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top