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

## Files

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

## Participate now!

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