Need help for Excel

[<SparTacuS>]

I'm not an Excel guru but use it a lot for simple record keeping (student grades etc).

In the SS I'm working on at the moment one cell finds the highest mark from a set of marks using the =Max(Cell range) function.

What I would like to do is (automatically) fill a second cell with the 'Header' cell corresponding to the highest mark.

It's probably easier to understand with a picture (a very much simplified version):

Any help would be welcome (I'm not afraid of macros or VBA).

Sorry forgot to say - MS Office/Excel 2003

Edited April 28, 2008 by <SparTacuS>

[IcemanND]

=INDEX(C1:E1,1,MATCH(F2,C2:E2,0))

where c2:e2 in the MATCH statement is the same cell range as the MAX() range.

and C1:E1 is the cell range containing your headers

[<SparTacuS>]

Works like a charm. Thank you.

[spacesurfer]

would you ever have two values that are the same? for example, two 12's. if so, then this wouldn't work.

[<SparTacuS>]

I take your point spacesurfer, I'll have to try it out and see what happens if there are multiple instances of the highest value - will it crash, or just pick the first instance?

In the case of my SS as long as it picks one of them it won't matter (This is just to decide the best assignment to submit to exam board - purely on highest score) but it may make a difference in another application.

[spacesurfer]

it won't crash. it'll probably find the first max value and display that.