CODEWITHSUNDEEP
excel
Jovanny
Jovanny

Reputation: 167

Use index + Match to get value given certain criteria

I have a table (Table1) consisting of student names and their average scores for different subjects. I want to get the name of the student with the maximum score for a particular subject.

I was able to get the maximum score for that subject using MAX(IF(Table1[Subject]=F5,Table1[Average Score])). The problem is that when I tried to get name of the student using index =INDEX(Table1, MATCH(MAX(IF(Table1[Subject]=F5,Table1[Average Score])), Table1[Average Score], 0), 1) I got the name of another student having the same score but for a different subject.

How do I restrict the result so that I get the student name that corresponds to that subject?

enter image description here

In the snapshot above, I intend to get Maxim Sunny (in cell F7) for Mathematics (cell F5) but I got Timothy Jovelyn instead.

Upvotes: 0

Views: 66

Answers (1)

basic
basic

Reputation: 11968

You can use following array formula:

=INDEX(Table1, MATCH(MAX(Table1[Score]*(Table1[Subject]=F5)),(Table1[Score])*(Table1[Subject]=F5), 0), 1)

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Edit:

Formula to find Name with minimum Score:

=INDEX(Table1,MATCH(AGGREGATE(15,6,Table1[Score]/(Table1[Subject]=F5),1),(Table1[Score])*(Table1[Subject]=F5),0),1)

Upvotes: 1

Related Questions