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.