Lookup formula base on two worksheet and multiple cells.

[asyamonique]

Hello,
Good Day,
Could you please help me with given sample below?
This is simply staff schedul which with two worksheets.
Sheet1 has the master datas all the way down.
I need to place the formulas on Sheet2 in cells E2 and F2 as name and surname...
A1 will be the date information on Sheet2
A2 is the time in information on Sheet2
A1 and A2 values may be changed anytime base on date or time-in.
Many thanks.

		1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	7-Aug
NAME & SURNAME	POSITION	Mon	Tue	Wed	Thu	Fri	Sat	Sun
NAME1	TITLE1				8
NAME2	TITLE2				8
NAME3	TITLE3				8
NAME4	TITLE4				8
NAME5	TITLE5				10
NAME6	TITLE6				10
NAME7	TITLE7				10
NAME8	TITLE8				10
NAME9	TITLE9				12
NAME10	TITLE10				12
NAME11	TITLE11				12
NAME12	TITLE12				12
NAME13	TITLE13				12
NAME14	TITLE14				12

4-Aug				NAME & SURNAME	TITLE
8				?	?
				?	?
				?	?
				?	?

 

[SanjayGMusafir]

Hello,
Good Day,
Could you please help me with given sample below?
This is simply staff schedul which with two worksheets.
Sheet1 has the master datas all the way down.
I need to place the formulas on Sheet2 in cells E2 and F2 as name and surname...
A1 will be the date information on Sheet2
A2 is the time in information on Sheet2
A1 and A2 values may be changed anytime base on date or time-in.
Many thanks.

		1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	7-Aug
NAME & SURNAME	POSITION	Mon	Tue	Wed	Thu	Fri	Sat	Sun
NAME1	TITLE1				8
NAME2	TITLE2				8
NAME3	TITLE3				8
NAME4	TITLE4				8
NAME5	TITLE5				10
NAME6	TITLE6				10
NAME7	TITLE7				10
NAME8	TITLE8				10
NAME9	TITLE9				12
NAME10	TITLE10				12
NAME11	TITLE11				12
NAME12	TITLE12				12
NAME13	TITLE13				12
NAME14	TITLE14				12

4-Aug				NAME & SURNAME	TITLE
8				?	?
				?	?
				?	?
				?	?

What I understand from you sample data is that you want a spill result. Excel 2013 does not support spill, if I'm not wrong.

 

[asyamonique]

2007?

 

[gargamalebarbosa]

This is array formula. exit from formula with CTLRL+SHIFT+ENTER