Finding maximum value in row, and give the column names in which these maximum values are

Hi everyone,

I am setting up a small new database. Each row is a person, with in each column a grade from 1-10. Each column represents a specific exam. I want for all these persons the exam names (which are in row 1) for which they have scored best.

For instance

Person # - Exam 1 - Exam 2 - Exam 3 - Best Exam
Person 1 - 9 - 6 - 4 - Exam 1
Person 2 - 4 - 6 - 6 - Exam2, Exam3

I hope I am clear in defining my problem. I have tried Lookup, but it sometimes gave me wrong results. I could not figure out where I went wrong.

So, in my worksheet, the exam grades start in the 4th column.

Right now, for person 2 I have the next formula: =LOOKUP(O3;D3:N3;$D$1:$N$1)
With 03 being the maximum value of the grades.
With D3:N3 being all the grades that that person2 had (11 grades in total)
D1:N1 consists of the exam names.

When I use this, I sometimes got erroneous results. And even if it works, I will not get the result that I actually want: If a person gets the same grade for two exams, I want the names of both exams.

Hi,

Welcome to the Forum.

You would get better help if you attach a small sample workbook with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

excel.xlsx

Here's an example. I did manually the result I want to get.
Hope this helps

What if someone gets the same grade for 4 or 5 EXAMS? Do you want all 5 of them to be shown in one cell? Or is there any maximum for this? say only top 2 or top 3.

All of them is my focus at the moment.

And if it is very simple to explain the difference with making a top-3, then I would not mind to learn how that can be accomplished as well

Hi,

See the attached file. I have used the following array formula - to be confirmed by pressing CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

This will flag the top 3 Exams and show the output in column 'O'

Maybe so fit?