Forum Discussion

rjtomkinson's avatar
rjtomkinson
Copper Contributor
Nov 14, 2023

Excel formula for finding, ranking and returning nth value

Can anyone explain what formula or array formula I'd need for the following:   Scenario - school cross country results  Each school may have six runners but only the top four score.  Column A lis...
excel
Formulas and Functions
peiyezhu's avatar
peiyezhu
Bronze Contributor
Nov 21, 2023
Yes - I need the 1st, 2nd, 3rd and 4th position runners for each school.


Which tabel is your expected result according to those raw datas?


Which tables are raw datas?
There are many worksheets and each sheet also includes 3 seperate range/tables.
rjtomkinson's avatar
rjtomkinson
Copper Contributor
Nov 21, 2023

peiyezhu 

 

Again - this is why I didn't want to upload the original document.

Use the document attached instead please.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 21, 2023

    rjtomkinson Provided that you are using Excel for 365 the basic formula for your example would be:

     

    =TAKE(SORT(FILTER(A2:A45,B2:B45=E1)),4)

     

    Though note the slight modifications to cells D1 and E1 where the latter now only contains the the school number, thus not "School 13".

     

    Edit:

    Added your file with two alternative formulas that should work in older Excel versions.

     

    • rjtomkinson's avatar
      rjtomkinson
      Copper Contributor
      Nov 21, 2023

      Riny_van_Eekelen 

      Thank you for this.

      How can I extrapolate this formula? I get the alert that I cannot edit an array formula.

      I need to be able to copy it across for multiple schools

      e.g. School: 13, 18, 41 etc 

      each time searching the same table for the 1st to 4th runners for that respective school number.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Nov 21, 2023

        rjtomkinson So you are using the formula surrounded by the curly brackets in I2? That's an old style array formula that needs to be confirmed with Ctrl-Shift-Enter (CSE), i.e. not just Enter. As soon as you touch it you get that error message. You need to select all four cells, enter the formula  and then CSE. Then you can select those four cells again and drag them across. It's actually quite annoying to work with such CSE type formulas. By the way, you need to make the references to columns A and B absolute for copy by dragging to work correctly. Done that in the attached file.

         

        I've also include a traditional solution that avoids CSE. I would use that one if you really can't get your hands on a modern Excel version.

         

         

Resources