Search for SECOND highest number in an array

**King Simon** · 03-25-2013, 09:28 PM

Hi All,

I am trying to find the second highest number in an array and then use a lookup function to let me know in which year the second highest number occurred. My data is per below:

Dec-02 0
Dec-03 144
Dec-04 100
Dec-05 415
Dec-06 307
Dec-07 276
Dec-08 197
Dec-09 186
Dec-10 354
Dec-11 275
Dec-12 552

Using this formula =TEXT(LOOKUP(LARGE(B1:B11,2),B1:B11,A1:A11),"yyyy") I get the result "2011" when it clearly should be "2005"...

I am using this formula elsewhere in the spreadsheet and it works just fine.

Any help would be greatly appreciated.

**martindwilson** · 03-25-2013, 09:37 PM

for lookup to work properly the values in col b need to be in ascending order
try
=TEXT(INDEX(A1:A11,MATCH(LARGE(B1:B11,2),B1:B11,0)),"yyyy")
or
=TEXT(VLOOKUP(LARGE(B1:B11,2),CHOOSE({1,2},B1:B11,A1:A11),2,0),"yyyy")

**Norie** · 03-25-2013, 09:42 PM

You need the range to be sorted ascending for LOOKUP to work.

Try this.

Formula:

Please Login or Register  to view this content.

**Tony Valko** · 03-25-2013, 09:54 PM

Here's another one:

=YEAR(INDEX(A1:A11,MATCH(LARGE(B1:B11,2),B1:B11,0)))

**King Simon** · 03-26-2013, 11:23 PM

Thanks Martin, Norie and Tony. All three worked perefctly.

Regards.

**Tony Valko** · 03-27-2013, 09:05 AM

You're welcome. We appreciate the feedback!

Search for SECOND highest number in an array

LinkBack

Thread Tools

Rate This Thread

Display

Search for SECOND highest number in an array

Re: Search for SECOND highest number in an array

Re: Search for SECOND highest number in an array

Re: Search for SECOND highest number in an array

Re: Search for SECOND highest number in an array

Re: Search for SECOND highest number in an array

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions