"Harlan Grove" wrote:

"Duke Carey" wrote...
Assume you have a column header in row one, & that cell is named 'hdr'
Assume your column of values is named tbl, and starts in row 2

=ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),C OLUMN(hdr))
....

MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order,
in which case ROWS(tbl) would be much simpler. Perhaps you meant
MATCH(MAX(tbl),tbl,0)?

More compact to use

=CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0)))



This works brilliantly, and by substituting 'Row' for 'Address' I can get
the row number. Now I want to refer to another cell in that same row, and
extract the value from it. I tried:

=C(cell("row",index(tbl(match(max(tbl),tbl,0))))

where 'C' is the column from which I want the value, but it doesn't work.

Background: My worksheet has twelve month columns, and a total column. Rows
are years, 'tbl' is the total column. The formula (as amended) gives me the
row containing the highest annual total, and I want to refer to the
individual months in that year.

Any ideas, please ?