Index Match to get Information from another worksheet

I have a workbook with 5 worksheets, 1 an employee name list, and 4 others with an identical layout, the only difference is the cells mean something else (hours, name, production, average)but each cell directly relate to the identical cell in the other worksheets based upon column A "date". I want to pull the "line and zone" that the employee worked on based upon what date is selected in D2 of the "Data" tab.
More Detail:
Ok lets say I have 4 Lines of Production named Line A, Line B, Line C, Line D and on each line I have 5 workers (each in a Zone) Zone 1, Zone 2, Zone 3, Zone 4, Zone 5. I have 4 worksheets all layed out the same, with the Line and Zones going horizontally, and each date going vertically.
In the "Avg" Tab, Line C4 tells you what the average production was done on Line A, Zone 1 on 1/1/2011
In the "Production" Tab, Line C4 tells you what the total production was done on Line A, Zone 1on 1/1/2011
In the "Hours" Tab, Line C4 tells you the total hours worked on Line A, Zone 1on 1/1/2011
In the "Name" Tab, Line C4 tells you who worked on Line A, Zone 1on 1/1/2011

So you can see, in each worksheet cell C4 is directly related to each other. I would like to retrieve a collection of this information in another worksheet called "Data" based on inputting a date into the worksheet.

If you view the attachment, you will see I have a "sample" tab of what I would like done, based on the given information. I have highlighted the cells in each workbook, to help visualize what is happening and how the information is directly related. Each color represents an employee, so we can see where that employee worked.

I have created another tab "Data" to show how I am pulling the other information based upon the dates that I input (thanks to ConneXionLost's help)

I would like to return infomation specific to the "line and zone" worked, as shown in the "Sample" tab. I tried using the same equation but changing the index to reflect the row where the "Line" number would be displayed, but I get a #REF error....

So here is my problems:

1. The "Line" names are merged between 5 columns (representing each zone), so when I try to index match, it will only return the value for the first zone in each Line, not sure how to fix this, without calling each column its name and hiding a row, is there another way?
2. When changing the date, the Zone fields return all #REF errors, I have no idea what is causing this as the other fields are working fine

Any suggestions?

Obviously in terms of "best practice":

a) merged cells should always be avoided
b) if you need to retrieve data based on headers those headers should repeat as necessary

However, given the file as you have it:

Please Login or Register  to view this content.

Regards #REF! errors - your INDEX range for Zone is a Horizontal Vector ergo row_num is always 1 and/or can be omitted, see below:

Please Login or Register  to view this content.

as you have it presently - when you change date the MATCH of date occurs in a row other than 1 and thus you reference rows outside of the INDEX range leading to #REF! errors

In terms of handling the #N/As - you have a few options (assuming backwards compatibility requirement w/version prior to XL2007)

You could use a pre-emptive COUNTIF test, again in pseudo-terms:

Please Login or Register  to view this content.

you need only conduct the above in the first result column, thereafter you can ascertain if that column is Null and if so return Null [no need to repeat the COUNTIF]

For # columns:

Please Login or Register  to view this content.

For Text columns as above but change 9.99E+307 to REPT("Z",255) and 0 to ""

You could double evaluate with ISERROR but you should not.

You could also use Conditional Formatting to mask the #N/As

I would say the COUNTIF approach in first column only makes most sense in this instance

Almost everything works brilliantly, thanks for pointing me in the right direction, also for answering a question about the "#NAs", but I can not seem to get the zone and Line equations working correctly,

Originally Posted by DonkeyOte

Please Login or Register  to view this content.

you need only conduct the above in the first result column, thereafter you can ascertain if that column is Null and if so return Null [no need to repeat the COUNTIF]

Here is how I have it in cells E5=

Please Login or Register  to view this content.

in cell F5=

Please Login or Register  to view this content.

both are returning a #Name error, what am I overlooking?

please note: I am able to return the desired results with the if(iserror) as you mentioned but it is ugly

Please Login or Register  to view this content.

In my post you will note I referred to the formula in "pseudo terms" - ie not a *real* formula i.e: "range", "date", "name" to be replaced with appropriate references.

I also stated you need only perform the pre-emptive COUNTIF test once per row (in the first column - B).
Thereafter you need only refer to the value in B, if B is Null then so should the other cells be.
Not doing this means you're needlessly repeating the COUNTIF calculation (it's constant per row).

So....

Please Login or Register  to view this content.

C5:D5 as they were and E5:F5 as suggested (first 2 formulae in my prior post) only now in lieu of alteration to B each formula should have the additional:

Please Login or Register  to view this content.

such that the result of B determines whether or not the function need be calculated and/or Null returned instead.

I understand now,(after rereading a few times) Thanks!