Need help - Looking for a formula that will will extract data by date and employee number

[mnsportsfan]

I have attached a spreadsheet for work that shows the data we track. It lists each day and what each person did for the day on sheet 2. I'm looking for a formula to take that data from sheet 2 and plug it into sheet 1 for an individual person. If I enter the person's employee number in cell A1 on sheet 1 it would pull the data from sheet 2 for that person for each day of the week and insert them into columns B,C,D, etc.. So I need a formula that matches the employee number from cell A1 in sheet 1 to row B in sheet 2 and matches the date from row A in sheet 1 to the date in row 1 in sheet 2 then plugs in the data across the sheet in sheet 1. Make sense?

In the example the formula would show what employee number 45744 did on December 29th, 28th, 27th, 26th. All that data is pulled from sheet 2 and put into sheet 1 in cells B18-X18 for the 29th.

[JeteMc]

This proposed solution employs a helper row on sheet 2, which may be hidden for aesthetic purposes.
The formula that populates the helper row (1) is:

Please Login or Register  to view this content.

The array entered formula* that populates the table on sheet 1 is:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[kvsrinivasamurthy]

In B2, then drag across fully.

Please Login or Register  to view this content.

[mnsportsfan]

This worked perfectly. I have 1 add on question. Lets say I had multiple sheets with this information on it and I wanted to reference all of them. Wherever it says sheet 2 in the formula couldn't I add the sheets I wanted it to reference to that?

Ex:
=IFERROR(INDEX(Sheet2:Sheet5!$E$3:$OE$250,MATCH(Sheet1!$A$1,Sheet2:Sheet5!$B$3:$B$250,0),MATCH(Sheet1!$A2,Sheet2:Sheet5!$E$1:$OE$1,0)+MATCH(B$1,$B$1:$X$1,0)-1),"")

Then it should reference sheets 2 through 5 correct? I did that but it didn't work.

[kvsrinivasamurthy]

Formla will not work for 3D. UDF is required for this.