Nov 16 2008

# Find the 2nd Largest Value in a Range

Excel provides an easy way to list the k-th (first, second, third, etc.) largest number in a range.  We’ll also take a look at the opposite situation where you want to find the 2nd smallest value in a range, for example.

Large & Small Formulas
So let’s go ahead and take a look at the formulas:

=Large(range, k)

where range is the list of number you want to find the values of, and k is the largest number you want to find. Similarly, the Small formula looks like this:

=Small(range, k)

where range is again the list of numbers you want to find the values up, and k is the smallest number you want to find.  Now’s let’s look at a few examples.

Examples

Large and Small Formula Example

=LARGE(B2:B6,2) will return 86, since 86 is the 2nd largest number in that range of numbers.

=LARGE(B2:B6,5) will return 12, since it is the 5th largest number in the range (also the smallest in this example)

=SMALL(B2:B6,1) will return 12, since it is the smallest number in the range.

=SMALL(B2:B6,3) will return 45 for the 3rd largest number.

** Excel Hints provides Excel Tips and Excel Help for All Levels.

### More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

### 11 Comments on this post

1. Kevin said:

AHT (Sec) AHT Ranking Amt of Day At Work
640 65 5
699 85 4
852 117 5
774 100 5
737 94 5

How do i return a value in the first colum as the lowest number if the matching row value is 5 in the third column?

Thanks,
Kevin

January 23rd, 2009 at 1:43 pm
2. john said:

This is a tricky formula. Use the small formula in a similar manner to this:

=SMALL((B1:B10=5)*A1:A10,ROWS(B1:B10)-COUNTIF(B1:B10,5)+1)

where Column B contains the 4′s and 5′s and column A is the column of data you are trying to get the min from. After you enter this formula, you will have to hit Ctrl+Shirt+Enter to get it to work. Brackets {} will show up surrounding the formula in the formula bar if you have done it correctly.

Hope this helps,
John

January 23rd, 2009 at 3:07 pm
3. Joe said:

Hi,
I have a similar problem,
How can I find the largest number in a range of numbers in a single col, and delete that number from the cell leaving the rest of the numbers unchanged?
I will need to put this in a macro.

I’m having trouble using MATCH, LARGE, ADDRESS.

Thanks for the help
JOe

June 2nd, 2009 at 11:13 am
4. john said:

Hi Joe. This doesn’t sound like it would be too difficult to handle with a macro. Would you want to shift up the deleted number or just clear the cell of that value?

June 3rd, 2009 at 8:46 pm
5. john Caulfield said:

Large worked fine but is there a function to sum for example the three largest values in a range of six cells?

June 11th, 2010 at 4:26 pm
6. Prasanna said:

Hi

I have a problem with a formula. I need to find out the second largest number in a list. For eg. if the numbers are
apple – 12
banana – 12
orange – 11
I want to list Banana as it is the second largest number after 12.

regards
Prasanna

August 16th, 2011 at 10:57 am
7. john said:

I would break the data into multiple columns using the text to columns features and sort the multiple columns as you desire.

August 19th, 2011 at 10:51 pm
8. RAJEEV said:

THANK YOU VERY MUCH FOR SOLVING MY PROBLEM..

June 2nd, 2012 at 2:39 am
9. Nancy said:

I would like to find 10 largest customers in total_amount_EQ on the list below

FULL_NAME TOTAL_AMOUNT_EQ
CTY CP CHUNG KHOAN PHU HUNG 598,682,000
BUI THI THU HA 119,736,400
VU PHUONG THAO 239,472,800
DANG TRAN HOAN 239,472,800
LE PHUOC BON 18,037,642,000
NGUYEN ANH LUONG 18,774,000,000
NGUYEN THI THUY MINH 12,369,980,000
NGUYEN VAN HONG 15,957,900,000
TRAN THI NGOC DIEM 1,877,400,000
TRAN THI ANH 13,559,000,000
PHUONG XUAN THAO 6,258,000,000
NGUYEN VAN TEO 18,774,000,000
NGUYEN MINH HIEU 5,215,000,000
PHAM QUANG CHANH 14,602,000,000
DINH THANH UYEN 13,559,000,000
VO DUC XUYEN 3,129,000,000
LE TRUNG DUNG 3,129,000,000
NGUYEN PHAT THANH 1,877,400,000
LAM CHI DUNG 3,337,600,000
CTY TNHH OTO SAI GON 7,509,600,000
BANH CUONG 2,607,500,000
DANG THUY NGA 147,921,598
NGUYEN HOANG PHUC 103,257,000,000

Thank you

October 5th, 2012 at 10:20 pm
10. marc said:

In a range of cells where numbers can be duplicated and cells can be blank, I need to find the Min and Max values of only the largest 100 unique values. Thanks

November 10th, 2012 at 2:40 am
11. Mark said:

I want to sort a top 10 with a number of columns B,C etc with various %. I want to sort each column with Header A
A B C
Ball 100% 100%
Bat 98% 45%
Doll 56% 100%
Toy 100% 100%
Car 66% 73%
Train 23% 44%
Van 100% 65%
Pram 100% 81%
Stick 36% 24%
Chair 100% 100%
table 88% 59%

I’ve used LARGE on the numbers and then 1, 2, 3 and so on. I used a vlookup to find number and then give me the item. This is ok until a number is the same.
How can I put the above in numerical order and then Alpha.
My formula for some reason on Col B gives me
Ball 100%
Ball 100%
Ball 100%
Ball 100%
Ball 100%
Bat 98%
Table 88%
Car 66%
Doll 56%
Stick 36%

I also need the same for Small which would include Train but not Ball

October 29th, 2014 at 11:38 am
Subscribe Form