# Identify highest score category within group

• Hi All,

I have a data table from column A-D. I want to identify the category in which each student has highest marks.

For some data manipulation purpose, I would like to have the category printed on a row which had the highest marks for a particular student.

Attached is my data table and I have manually typed the end result that I want to achieve by formula. Please note that there are varying rows as per students, E.g. Alex has 3 rows, Bob has 2 and Kim has 4. If the formula can print the result in a same row which had highest marks per student it would be great.

Best,

## Files

• Re: Identify highest score category within group

Try this Array* formula:

[COLOR="#0000FF"]=IF(D2=INDEX(\$D\$2:\$D\$10,MIN(IF((\$A\$2:\$A\$10=A2)*(\$C\$2:\$C\$10=MAX(IF(\$A\$2:\$A\$10=A2,\$C\$2:\$C\$10))),ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1))),INDEX(\$D\$2:\$D\$10,MIN(IF((\$A\$2:\$A\$10=A2)*(\$C\$2:\$C\$10=MAX(IF(\$A\$2:\$A\$10=A2,\$C\$2:\$C\$10))),ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1))),"")[/COLOR]

[arf]*[/arf]

copied down

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Identify highest score category within group

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!