index / array return a value from same column in another sheet

Good Morning,

I am currently looking at a simple data set spanning a few years of annual average data. I want to create a front sheet to show people in what year the maximum value was reached for each site but am having trouble returning the values.

The main data table is headed as:
Site Name : 2002 : 2003 : 2004 ~ 2009
XYZ st : 25.6 : 17.2 : 15.8 ~ 20.6
ZXQ road : 32.7 : 10.9 : 25.6 ~ 20.2

The summary sheet is headed:
Site Name : Max Value : Year
XZY st : =(maxB2:B10) : *stuck on formula (find max value in other sheet and return corresponding year from top row)*

I want the formula to say - find the summary sheet max value cell in the main data sheet row 2 (for XYZ) and return the corresponding column from row 1 (year).

I've got myself in a muddle trying to create array data from the index function and want something simple. There are only about 30 rows so am happy to manually select the rows in question and repeat the formula entry so it doesn't need to be fancy.

I appreciate any feedback you can give me.

Thanks in Advance,

Damien

An INDEX should work... if we assume your list of sites on the summary tab reflects the list on the data tab (in terms of ordering) then the MAX value for each site should simply be the MAX of the associated row on the data tab

Summary!A2: =Data!$A2
Summary!B2: =MAX(Data!$B2:$I2)
Summary!C2: =INDEX(Data!$B$1:$I$1,MATCH($B2,Data!$B2:$I2,0))

above copied down for all sites... note: where MAX occurs in 1+ years the above will return first year in which the Max occurred.

if in doubt - post a sample workbook with dummy data (to avoid confidentiality issues) - this way we can see what you're working with...