Lookup value in multiple worksheets, return values in Row1, 2, & 3 in corresp. column

I have a workbook (Excel 2003) with 6 worksheets. Sheet2 through Sheet6 include analytical data from a varying number of samples (i.e. Sheet2 has data from 7 samples while Sheet6 has data from 20 samples). Each sample has 16 constituents of concern (COCs) for which there is a corresponding value. Column A for Sheet2 through Sheet6 includes the following row labels:

Row 1 = Sample Name
Row 2 = Sample Depth
Row 3 = Sample Date
Row 4 = blank
Rows 5 to 23 = COC Names

Columns B and beyond for Sheet2 through Sheet6 represent a specific sample ID (i.e. SB-1, SB-2, etc.)

The data in Sheet2 through Sheet6 represent a general time interval (i.e. all data generated during 1990 is on Sheet2, all data generated during 1998 is on Sheet3, etc.).

Sheet 1 lists the maximum concentration for each COC out of all 5 remaining worksheets. Column A in Sheet 1 includes each of the COCs listed in Column A in Sheet2 through Sheet6. Column B in Sheet1 is titled "Max Concentration." I used the following formula in Sheet1; cell B3 to return the maximum concentration for the COC in row 6 of Sheet2 through Sheet6 (in the example, Sheet2 = '1990', Sheet3 = '1998', etc.):

=MAX('1990'!B6:H6,'1998'!B6:G6,'1999'!B6:M6,'2000'!B6:H6,'2002'!G6:U6)

I then copied this formula down the applicable # of rows in Sheet1, column B to obtain all of the maximum concentrations for each COC.

THE PROBLEM:

I want a formula to identify the sample ID, sample depth, and sample date that corresponds to each maximum concentration. As stated above, Sheet1, Column B includes the maximum concentration for each COC. Sheet1, Columns C, D, and E, will represent the Sample ID, Sample Depth, and Sample Date, respectively. I have the following formula in Sheet1, cell C3, to return the Sample ID corresponding to the value in Sheet1, B3, but I can only get it to work with one (1) worksheet:

=IF(ISERROR(LOOKUP(B3,'2000'!$B6:$H6,'2000'!$B$1:H$1)),"NA",(LOOKUP(B3,'2000'!$B6:$H6,'2000'!B$1:H$1)))

I have attached a workbook with what I have so far. The above formula produces the desired results in Sheet1, cells C3, D3, & E3; C4, D4, & E4; and C16, D16, & E16 since the maximum concentrations happen to be in the sheet labeled '2000'; however, the remaining results are incorrect since I have not been able to include the other sheets in the formula.

Is there a way to include all 5 worksheets in the above formula?

Thanks in advance for any input.

Re: Lookup value in multiple worksheets, return values in Row1, 2, & 3 in corresp. co

Thanks for the help Herbds7. I have not been able to open your file as I am on Excel 2003 and even though I have the file format converter installed, I get an error message when I try to open the file (either "The converter failed to open the file" if I try to open from Excel, or "The converter failed to save the file" if I try to open from explorer while Excel is closed).

Thanks again.

Re: Lookup value in multiple worksheets, return values in Row1, 2, & 3 in corresp. co

Given that I do not have Excel 2010 (I have 2003), does any one know of a way to modify the following formula so that it will look at all 5 sheets in the workbook (not just the '2000' sheet)?

=IF(ISERROR(LOOKUP(B3,'2000'!$B6:$H6,'2000'!$B$1:H$1)),"NA",(LOOKUP(B3,'2000'!$B6:$H6,'2000'!B$1:H$1)))

I only need the formula to look at the same row in each sheet (i.e. Row 6 as in the above example). Once the formula is working, I can change the row reference as needed to return the additional results.