I need a formula that shows the header value which matches the maximum value in a row

[anabmcosta]

Hi all! I am new here and I am pretty new to excel too, so this question might be foolish for some and I am sorry for that.

I am creating a spreadsheet with the grades of students in each subject. I added a column where it shows the highest grade of each student using the formula MAX.

But I want to know in which subject that student got her/his highest grade.
I am attaching the exemple and I added the information that I am looking for manually.

Thank you very much!

[Glenn Kennedy]

Slight change of result layout:

=IFERROR(INDEX($2:$2,SMALL(INDEX(($C3:$F3<>$G3)*10^10+COLUMN($C3:$F3),0),COLUMNS($H:H))),"")

copied across and down. If you MUST have tied subjects in the same cell, you will need either a VBA-based UDF or a helper column. Your choice!!

[anabmcosta]

Thanks a lot. It works!

[Limor_OP]

Hi
Please try this in H3 and down (This can bring up to 4 subjects in a cell) :

=INDEX($A$2:$F$2,,AGGREGATE(14,6,((COLUMN($C:$F))/(RANK($C3:$F3,$C3:$F3)=1)),1))&IFERROR(","&INDEX($A$2:$F$2,,AGGREGATE(14,6,((COLUMN($C:$F))/(RANK($C3:$F3,$C3:$F3)=1)),2)),"")&IFERROR(","&INDEX($A$2:$F$2,,AGGREGATE(14,6,((COLUMN($C:$F))/(RANK($C3:$F3,$C3:$F3)=1)),3)),"")&IFERROR(","&INDEX($A$2:$F$2,,AGGREGATE(14,6,((COLUMN($C:$F))/(RANK($C3:$F3,$C3:$F3)=1)),4)),"")

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.