Name lookup formula

[MarkAn]

Hi

I have a spreadsheet with 2 worksheets.

Sheet1 holds a list of Names I.e. Mark, Paul, Peter etc
Sheet2 holds daily data, Column A = 1st day, Column B = 2nd day etc (this information changes month on month when new data is pasted in), it holds the name of the person and then below it holds a number.

The Names on Sheet1 appear in each column on Sheet2, however, not in the same order.

I am looking for a formula that I can input into Sheet1 Column B that will look at Column A of Sheet2 and bring back the value directly under the name. Ie. for Mark - Column A = 8, Column B = 6, Column C = 10 and so forth.

Hope you understand what I am trying to achieve.

Thanks
Mark

 

[AlanY]

try this, copy B1 down and across

Excel 2013/2016
	A	B	C	D	E
1
2
3
4	Mark
5	22/10/2017	29/09/2017	14/10/2017	10/10/2017	13/10/2017
6
7
8
9
10
11	Paul
12	18/10/2017	13/10/2017	20/10/2017	07/10/2017	21/10/2017
Sheet2

Excel 2013/2016
	A	B	C	D	E	F
1	Paul	18/10/2017	13/10/2017	20/10/2017	07/10/2017	21/10/2017
2	Mark	22/10/2017	29/09/2017	14/10/2017	10/10/2017	13/10/2017
Sheet1

Cell Formulas
Range	Formula
B1	=OFFSET(Sheet2!$A$1,MATCH($A1,Sheet2!$A:$A,0),COLUMN(B$1)-COLUMN($B$1))