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

    Code
    =INDEX(N130:N161,MATCH(TRUE,N130:N161>N126,0))

    . 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:


    =LARGE(IF(N130:N161<N126,N130:N161),2)


    [arf]*[/arf]

    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:


    =INDEX($N$130:$N$161,MATCH(N126,$N$130:$N$161,1)-1)


    =INDEX($N$130:$N$161,MATCH(N126,$N$130:$N$161,1)+1)

    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!

Register Yourself Login