To find the second highest (second largest) value in Excel, use =LARGE(C2:C9,2). LARGE returns the Nth largest number in a range, so change the 2 to 3 for the third largest, 4 for the fourth, and so on. For the second smallest, use =SMALL(C2:C9,2).
That one formula covers most cases. But a few situations trip people up: duplicate values, finding the second largest per group, and getting the name next to the number. This guide handles each one, using the same small table throughout so you can follow along.
Quick Answers: Which Formula Do I Need?
| I want… |
Formula (sample table below) |
Works in |
| Second largest value |
=LARGE(C2:C9,2) |
All versions |
| Second smallest value |
=SMALL(C2:C9,2) |
All versions |
| Second largest distinct value (skip ties) |
=LARGE(C2:C9,COUNTIF(C2:C9,MAX(C2:C9))+1) |
All versions |
| Second largest distinct value (shorter) |
=MAXIFS(C2:C9,C2:C9,"<"&MAX(C2:C9)) |
Excel 2019 and later |
| Second largest distinct value (dynamic arrays) |
=LARGE(UNIQUE(C2:C9),2) |
Microsoft 365, Excel 2021 and later |
| Second largest for one group |
=LARGE(IF(B2:B9="East",C2:C9),2) |
All versions (Ctrl+Shift+Enter in Excel 2019 and earlier) |
| Name next to the second largest |
=INDEX(A2:A9,MATCH(LARGE(C2:C9,2),C2:C9,0)) |
All versions |
| Top 3 rows with names |
=TAKE(SORTBY(A2:C9,C2:C9,-1),3) |
Microsoft 365, Excel 2024 |
| Higher of two numbers |
=MAX(A1,B1) |
All versions |
The Sample Data Used in This Guide
Every example below uses this sales list in cells A1:C9 (headers in row 1). Notice two things: the highest sale of 800 appears twice, and 650 appears twice. Both matter later.
| A: Rep |
B: Region |
C: Sales |
| Asha |
East |
500 |
| Ben |
West |
800 |
| Chen |
East |
800 |
| Divya |
West |
650 |
| Elena |
East |
720 |
| Farid |
West |
300 |
| Gina |
East |
650 |
| Hao |
West |
400 |
Sorted from highest to lowest, the sales are 800, 800, 720, 650, 650, 500, 400, 300.
Find the Second Largest Value with LARGE
MAX only returns the biggest number, so it cannot give you the runner-up. LARGE can. Its syntax is LARGE(array, k), where array is your range and k is the position, counting down from the largest.
=LARGE(C2:C9,2) returns 800.
Why 800 and not 720? Because 800 appears twice, LARGE counts it as 1st and 2nd. That is correct behaviour for ranking, but often not what people mean by “second highest”. The next sections fix that.
=LARGE(C2:C9,1) returns 800 (same as MAX).=LARGE(C2:C9,3) returns 720.=LARGE(C2:C9,5) returns 650.
You can also point at a range that spans several columns, such as =LARGE(B2:D20,2). LARGE looks at every number in the block.
Find the Second Smallest Value with SMALL
SMALL works the same way from the bottom. =SMALL(C2:C9,2) returns 400 (the smallest is 300). The same rule applies: if the lowest value is repeated, SMALL counts each copy as its own position.
Second Highest Value When There Are Duplicates
If you want the second highest distinct value, so the two 800s count as one, pick one of these. All three return 720 on the sample data.
Option 1: LARGE with COUNTIF (works in every version)
=LARGE(C2:C9,COUNTIF(C2:C9,MAX(C2:C9))+1)
COUNTIF counts how many times the maximum appears (2). Adding 1 skips past all of them, so LARGE jumps to position 3, which is 720. If the maximum appears only once, it simply returns the 2nd largest.
Option 2: MAXIFS (Excel 2019 and later)
=MAXIFS(C2:C9,C2:C9,"<"&MAX(C2:C9))
This asks for the biggest value that is smaller than the overall maximum. MAXIFS is not available in Excel 2016 or earlier.
Option 3: UNIQUE with LARGE (Microsoft 365 and Excel 2021 and later)
=LARGE(UNIQUE(C2:C9),2)
UNIQUE strips the duplicates first, leaving 500, 800, 650, 720, 300 and 400 (in their original order). LARGE then picks the 2nd largest of those, which is 720. It is the easiest to read, and you can change the 2 to get the third or fourth distinct value.
Second Largest Value for Each Group or Criteria
To find the second largest sale for the East region only (answer: 720; East has 500, 800, 720 and 650), use one of these.
Microsoft 365 and Excel 2021 and later
=LARGE(FILTER(C2:C9,B2:B9="East"),2)
FILTER keeps only the East sales, and LARGE picks the second biggest. For West, the same formula returns 650.
Older versions (array formula)
=LARGE(IF(B2:B9="East",C2:C9),2)
Press Ctrl+Shift+Enter to confirm it in Excel 2019 and earlier. IF returns FALSE for non-East rows, and LARGE ignores those.
Any version, no Ctrl+Shift+Enter
=AGGREGATE(14,6,C2:C9/(B2:B9="East"),2)
Function 14 is LARGE and option 6 ignores errors. Dividing by FALSE (zero) creates errors for the non-East rows, which AGGREGATE then skips. Microsoft’s documentation notes that functions 14 and 15 need the k argument.
Get the Name Next to the Second Largest Value
Usually you need the person or product, not just the number.
INDEX and MATCH (all versions)
=INDEX(A2:A9,MATCH(LARGE(C2:C9,3),C2:C9,0))
This returns Elena, the rep behind the second highest distinct sale (720). I used 3 here because the two 800s take positions 1 and 2. If you use LARGE(C2:C9,2) you get 800, and MATCH returns only the first 800, which is Ben. Chen is silently ignored.
XLOOKUP (Microsoft 365, Excel 2021 and later)
=XLOOKUP(LARGE(C2:C9,3),C2:C9,A2:A9)
Also returns Elena. XLOOKUP defaults to an exact match, so you do not need a match-type argument. It is not available in Excel 2016 or 2019.
Return every tied name with FILTER
=FILTER(A2:A9,C2:C9=LARGE(C2:C9,2))
Returns Ben and Chen, spilling into two cells, because both sold 800. Wrap it in TEXTJOIN(", ",TRUE,...) if you want them in a single cell.
Top N List with Names
=TAKE(SORTBY(A2:C9,C2:C9,-1),3)
SORTBY sorts the whole table by Sales from high to low, and TAKE keeps the first 3 rows. The result spills as: Ben, West, 800; Chen, East, 800; Elena, East, 720. Change the 3 to any number you like. TAKE is available in Microsoft 365 and Excel 2024, not Excel 2021 or earlier.
Top 3 in any version: type =LARGE($C$2:$C$9,ROWS($1:1)) in a cell and copy it down three rows. ROWS counts 1, 2, 3 as you fill down, so you get 800, 800, 720. Use SMALL the same way for the bottom 3.
If you still sort and copy values by hand for weekly reports, these formulas save that time, because they update themselves when the data changes. Our quick tip on re-sorting a list or table covers the manual route.
Ignore Zeros, Blanks and Errors
- Blanks and text: LARGE and SMALL skip empty cells and text in a range, so you do not need to clean them out first. Numbers stored as text are also skipped, which is a common reason for a “wrong” answer.
- Zeros: A zero is a real number, so SMALL will happily return it. To ignore zeros, use
=SMALL(IF(C2:C9<>0,C2:C9),2) (Ctrl+Shift+Enter in Excel 2019 and earlier), or =AGGREGATE(15,6,C2:C9/(C2:C9<>0),2).
- Errors: If one cell in the range shows #N/A or #DIV/0!, LARGE returns that error too.
=AGGREGATE(14,6,C2:C9,2) ignores error values and returns the second largest of the rest.
Highlight the Second Largest Value
- Select A2:C9.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter
=$C2=LARGE($C$2:$C$9,2) and pick a fill colour.
On the sample data, both the Ben and Chen rows are highlighted because both have 800. To highlight the top three instead, use Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items and change 10 to 3.
Higher (or Lower) of Two Values
Not comparing a whole list, just two cells? Use =MAX(A1,B1) for the higher number and =MIN(A1,B1) for the lower one. No IF needed.
Common Mistakes and Errors
- #NUM! error. Either
k is 0 or negative, k is larger than the count of numbers in the range, or the range has no numbers. With only 8 numbers, =LARGE(C2:C9,9) returns #NUM!.
- Getting the maximum twice. This is the duplicate issue above. If your answer looks the same as MAX, use one of the distinct-value formulas.
- The wrong name returned. MATCH with 0 stops at the first match. For ties, use FILTER.
- #SPILL! error. FILTER, UNIQUE and TAKE spill into neighbouring cells. Clear the cells in their way.
- Numbers that are really text. If a value is left-aligned or has a green triangle, it is text and LARGE ignores it. Convert it to a number first.
The basic LARGE and SMALL formulas work the same way in Google Sheets and Excel for the web.
Frequently Asked Questions
How do I find the second highest value in Excel?
Use =LARGE(range,2). For example, =LARGE(C2:C9,2) returns the second largest number in C2:C9. LARGE counts duplicates as separate ranks, so if the top value appears twice you get that value again.
How do I find the second highest value if there are duplicates?
Use =LARGE(range,COUNTIF(range,MAX(range))+1), which works in every version. In Microsoft 365 or Excel 2021 and later you can use =LARGE(UNIQUE(range),2). In Excel 2019 and later, =MAXIFS(range,range,"<"&MAX(range)) also works.
How do I find the second highest value in a range for a specific category?
In Microsoft 365, use =LARGE(FILTER(values,categories="East"),2). In older versions, use =LARGE(IF(categories="East",values),2) with Ctrl+Shift+Enter, or =AGGREGATE(14,6,values/(categories="East"),2), which needs no special entry.
How do I return the name of the person with the second highest value?
Combine INDEX and MATCH: =INDEX(names,MATCH(LARGE(values,2),values,0)). In Microsoft 365 or Excel 2021 and later, =XLOOKUP(LARGE(values,2),values,names) does the same. If two people tie, only the first is returned, so use FILTER to list all of them.
What is the formula for the second smallest or second lowest value?
Use =SMALL(range,2). Change the 2 to find the third, fourth or any other lowest value.
How do I find the second max if there are blanks or errors in my data?
LARGE skips blanks and text automatically. For errors, use =AGGREGATE(14,6,range,2), where 14 means LARGE and 6 means ignore errors.
Keep Learning
Learn the lookup functions used above in Master These Excel Lookup Functions To Save Time, and see where LARGE fits among the essentials in Top 12 Most Important Excel Functions. If duplicates are your real problem, read How to Find and Remove Duplicates in Excel Quickly. To summarise whole tables without formulas, try How To Summarize Data in Excel: Top 10 Ways.
