Using Offset to make a dynamic formula

[PureBluff]

Evening all,

Ran into an issue I cant get my head around, Im tired and just cannot fathom how to make this work!

I have two data sets:

One is a projected percentil split by customer/day
Another is a Forecast versus Actual report

Dataset1:

Customer	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Customer 4			16%	8%	13%	19%	43%
Customer 5			20%	9%	36%		34%
Customer 6			20%	9%	36%		34%
Customer 7			20%	9%	36%		34%
Customer 8			14%	13%	18%	16%	39%
Customer 9			33%	33%		34%
Customer 10			42%			58%
Customer 11
Customer 12			34%		33%		33%
Customer 13			20%	20%	20%	20%	20%
Customer 14			20%	20%	20%	20%	20%
Customer 15			20%	20%	20%	20%	20%
Customer 16			20%	20%	20%	20%	20%
Customer 17			20%	20%	20%	20%	20%
Customer 18			20%	20%	20%	20%	20%
Customer 19			20%	20%	20%	20%	20%

Data Set two (where I need the formula):

I have the below:

		#N/A​	0​	0.33​
		Sun	Mon	Tue	Wed	Thu	Fri
Customer	SVC Code	29/12/2019​	30/12/2019​	31/12/2019​	01/01/2020​	02/01/2020​	03/01/2020​
Customer 4	40270051​
Customer 4	40272118​
Customer 4	40272119​
Customer 5	40035864​

Now, in the second row (yellow), I need to return the percentage split from a lookup of the customer/day in data set 1. Ideally with a formula that as I increment the column right along the 2nd row it increments the row index by +1

Any help greatly appreciated.
Thanks

 

[mikerickson]

If "Customer 4" is in cell A4 of the second sheet, and "Customer" is in A1 of the First sheet, try

=VLOOKUP($A5, Sheet1!$B$2:$H$20, MATCH(C$1, Sheet1!$A1:$H$1,0), False)

 

[Khizar Hayat]

you can use offset+match combination

 

[Khizar Hayat]

 

[Khizar Hayat]

you can do it so on