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