How to create a array formula to retrieve data?

Hi friends,
I want to find out the top students in the class in a subject. I’m not getting correct output on the summary sheet.

The data is on ‘Data’ sheet and I want the top students on ‘Summary’ sheet.

Simply I want to retrieve the data from ‘Data’ sheet to ‘Summary’ sheet.

Any help will be appreciated.

Thanking you.

Try this ARRAY formula in I6, copied down and across...
=INDEX(Data!J$4:J$48,MATCH($H6&MAX(IF(Data!$H$4:$H$48=$H6,Data!$M$4:$M$48,0)),Data!$H$4:$H$48&Data!$M$4:$M$48,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Hi Ford,
Thank you for solution. However it's not working properly. I want to match the subject on summary sheet '$m$4'. If there is 'Marathi' subject in m4 then all the top students for Marathi subject should be retrieve from 'Data' sheet on 'Summary' sheet automatically. and not the students for other subjects which didn't match cell 'm4' on 'Summary' sheet.

Please do the amendment in the array formula to achieve the target.

Thank you.

sorry, thought you wanted it based on highest score overall.

Try this instead...
=INDEX(Data!J$4:J$48,MATCH($M$4&LARGE(IF(Data!$I$4:$I$48=$M$4,Data!$M$4:$M$48,0),$H6),Data!$I$4:$I$48&Data!$M$4:$M$48,0))
ARRAY entered

Not, this does not work for duplicate scores

I can fix this if you are OK using a helper column?

I6=INDEX(Data!J$4:J$48,MATCH(LARGE(INDEX(((Data!$M$4:$M$48)+1/(ROW(Data!$M$4:$M$48)*10^10))*(Data!$I$4:$I$48=$M$4),0),$H6),INDEX(((Data!$M$4:$M$48)+1/(ROW(Data!$M$4:$M$48)*10^10))*(Data!$I$4:$I$48=$M$4),0),0))
Try this and copy across

Hi Ford,
No, I don’t want to add helping column because there is no place for it. I have another data there which I don’t want to move to avoide any programming error.

Hi Siva,
Good! It's working absolutely fine as per my requirement but one little amendment is require to make it fullproof.

See subject 'Hindi general and Hindi Special' If I enter 'Hindi' then it gives output of 'Marathi'.

Sorry for not mentioning it before that the 'Hindi general' and 'Hindi special' are count as one subject while ranking. See ranking formula for reference.

Amendment require:

If I enter a subject 'Hindi' instead of 'Hindi general' and 'Hindi special' then it should be count as one subject group.

Output require for Hindi:

1 Hindi general B2 67/100 Std. 5 B 0.67 1
2 Hindi general D2 61/100 Std. 5 D 0.61 2
3 Hindi special C2 57/100 Std. 5 C 0.57 3
4 Hindi general A2 55/100 Std. 5 A 0.55 4
5 Hindi special E2 33/100 Std. 5 E 0.33 5


Sorry for trouble.

Thank you.

see the attached file

Hi Shiva,
First of all I apologize for the delayed reply for the excellent creation. I was out of station so I couldn't reply you quickly.

Excellent! What a magic of array formula. It's working absolutely fine as per my requirement. I didn't understand the magic of it but it's perfect.

I appreciate you for the magic array formula creation to achieve the target.

Thank you.