Offset\Vlookup\Index Formula or should I use ??????

[Legacy 462336]

I have a file with two tabs. Tab one has data - Week, Rating, Score information that I manually enter in each week highlighted in yellow. Tab two has data - Week, Dept, Total Hr, Total Output, OPLH information that I use to get my OPLH grand total that I enter into the rating column in tab one. Example: Tab two Cell D46 is what I manually enter into Tab one Cell C3 and so on for each week.

Basically, I'm looking for a formula or method to take the second tab Cell D46 information each time the pivot table is generated for the week and be placed in the appropriate Rating column based in tab one. Something like In tab one Cell C3 I would have a formula =Vlookup(B3, "Select the range from tab 2" A1:J46, then I'm lost because I have not instructed excel that once it finds the correct date 10/5/2020 in this case, then select the data in cell D46. The same goes for using the offset formula and......so on. Any help is appreciated. Once the formula is generated I will copy it down column C on tab one and the appropriate rating will appear.

 

[GraH]

Hello,

Basically you have a range like this (pivot or not, does not really matter):

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4		5/10/2019			12/10/2019			19/10/2019
5
6
7
8
9
10	Grand Total	304,74	120025	393,9	304,25	129057	424,2	2015,44	133456	619,5
Sheet1

You can use an index-match-match combo.

Book1
	B	C
2	Week	Rating
3	5/10/2019	393,9
4	12/10/2019	424,2
5	19/10/2019	619,5
6	26/10/2019	#N/A
7	2/11/2019	#N/A
8	9/11/2019	#N/A
9	16/11/2019	#N/A
Sheet2

Cell Formulas
Range	Formula
C3:C9	C3	=INDEX(Sheet1!$A$4:$ZZ$5000,MATCH("Grand Total",Sheet1!$A$4:$A$5000,0),MATCH(Sheet2!B3,Sheet1!$A$4:$ZZ$4,0)+2)
B4:B9	B4	=B3+7

Alternative is to "point" to the correct pivot intersect, and having generate GETPIVOTDATA feature on will generate a formula with hard coded row/column references. You can make these dynalic by referring to cell addresses. Here is a tutorial.