Given a list of job applicants who have undergone a selection test
make a vertical list of all the applicants occupying the first 10 positions.


Sample excel attached.

Attached Files

SAMPLE-classification-list.XLSX (10.7 KB, 7 views)

Is this some kind of homework?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yes! I mentioned "applicants" but is intended for subjects very similar to applicants!
The problem though is exactly the same ...

Are you still using XL 2019?
Have a look at Pivot Table sorting or the LARGE function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yes, I'm still using xl 2019. As a very limited excel connoisseur, and a from time to time user, I'm not very familiar with PIVOT TABLES and LARGE functions ... I will deepen my knowledge of both functions and and make some attempts ... but I'm afraid this will not be of any help for my need right now! I'm not pretending that someone else does my homework for me ... in a longer o shorter period of time I will be surely able to do it myself ... but presently, if some moral principles do not inhibit it, an immediate and satisfactory help would receive a due appreciation! In any case, whatever the outcome, thank you very much, friends.

You may learn better by watching a short video, as I generally do.

https://www.youtube.com/watch?v=1_v-uqoyXqI

This looked pretty straightforward to me.

Thank you very much kilroyscarnival ... that was a very great help you gave me! Now, through that video you shared I managed to get more familiar with the italian excel functions INDICE (INDEX) and CONFRONTA (MATCH). Using them I've got some good results but not the exact ones! As a matter of fact there is a significat error the second top mark applicant (the n. 9), see attached sample, is not shown as such! Am I perhaps uncorrectly applying those functions? If so, can I get a tip on how to correct them? Thank you for availability.

Attached Files

SAMPLE-classification-list.XLSX (11.2 KB, 4 views)

Are you still using XL 2019 or a more recent version?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yes, I'm still using the official XL 2019 ...

Oops, I didn't notice that your list had duplicate entries. That's not going to work with a simple INDEX(MATCH) setup as it will always pull the first match. Also, I think you had a small error in your typed formula; after MATCH on the first row, I think you meant F5, not F6.

Also I just realized that your "problem" statement doesn't require that it be done in a single formula. The most basic way of achieving it would be to copy the applicant and test mark cells to another spot, sort on the test mark column, and delete the ones below the top 10. Another way would be to use a pivot table, and then sort on the Test Mark values column, and filter to the top 10. That might be the easiest way to do it, plus you could make your original data into a table so that when you add rows below they will be included and you only have to refresh. As you can see in the example, I added a rank column (with lots of ties) and the # of times the test is taken (the test mark is an average). Just some ideas.

Attached Files

SAMPLE-classification-list-w-pivot.xlsx (16.7 KB, 1 views)

Ok, kilroyscarnival, thank for your suggestions. I also thought tha such problem could only be solved with the use of an high level language, the availability and knowledge of which I do not have! Therefore, for the time being, I have to abandon the idea to solve this problem with a single excel formula! Of course personally I will continue to deepen my excel expertise so to try to implement you suggestions, but in this case we both surely agree that an end user can peacefully be called to simply use an excel procedure but not to make a maintenance of it!
So that’s, then, the end of it! Having nevertheless greatly appreciated your availability I duly wish to thank you and thank you very much for everything. A.P.

There certainly are ways of getting it done just with formulas, but because of the ranking on duplicate (tie) values, it adds some steps. Especially if you don't have the version of Excel that supports dynamic arrays. Here are some discussions of this:

INDEX MATCH function, avoid duplicate returns - Microsoft Community Hub

Additionally, you don't have, strictly speaking, ranks 1 through 10, but as my example showed, a four-way tie for #6, it makes it more complicated when you get to the top ten.

I don't use INDEX/MATCH a lot so I'm not the best person to walk you further through those steps. Best of luck with your learning!