Second Largest or Second Smallest Value

  • I'm using Excel 97-2003 version.

    In a spreadsheet, I have a threshold limit (Number) in Cell N126 which i want to compare with number values in Column Range N130:N161 and return second highest value in Cell P131 where i am getting the second highest value result with Array formula


    . Cntrl Shift Ent

    Similarly, I want to retrieve second smallest value, compared to cell content of N126 in cell Q131, where i am getting confused, and seek help.

    Any better suggestion to existing formula is also welcome whereby desired result can be obtained.

    Later, I want to simply combine both formula and get the desired result anywhere in spreadsheet based on criteria of Cell M124. To Illustrate, IF M124="B", then retrieve second highest value, else, second lowest value.

    If I have not explaied well here, i am attaching sample file with color coding and what exactly i seek to avoid confusion.

    Look forward for suggested corrected formula, thanks.

  • Try this Array* formula:



    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • I think it's just a case of these two regular formulae:



    Ali :)

    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!