<SparTacuS> Posted April 28, 2008 Share Posted April 28, 2008 (edited) 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> Link to comment Share on other sites More sharing options...
IcemanND Posted April 29, 2008 Share Posted April 29, 2008 =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 Link to comment Share on other sites More sharing options...
<SparTacuS> Posted April 29, 2008 Author Share Posted April 29, 2008 Works like a charm. Thank you. Link to comment Share on other sites More sharing options...
spacesurfer Posted April 29, 2008 Share Posted April 29, 2008 would you ever have two values that are the same? for example, two 12's. if so, then this wouldn't work. Link to comment Share on other sites More sharing options...
<SparTacuS> Posted April 29, 2008 Author Share Posted April 29, 2008 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. Link to comment Share on other sites More sharing options...
spacesurfer Posted May 2, 2008 Share Posted May 2, 2008 it won't crash. it'll probably find the first max value and display that. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now