Use LARGE and MAXIFS to Find the Second-Largest Value in Excel
Question details
Find the second-largest value that meets criteria or exclude the overall maximum from a MAXIFS calculation.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains values and criteria, and the user wants a result below the maximum or the second-ranked matching value.
- Observed behavior
- The desired result depends on whether the overall maximum is excluded or values are ranked only after applying the criteria.
Identify the value range and criteria ranges, and make sure they cover the same rows. Decide whether to exclude the overall maximum or rank only the values that meet your conditions.
Exclude the Overall Maximum with MAXIFS
Use this method when the largest value in the entire value range should be excluded, whether or not it meets the other criteria.
In your worksheet, put values in C3:C20, categories in A3:A20, and the category to match in E5.
Select an empty cell and enter =MAXIFS(C3:C20,C3:C20,"<"&MAX(C3:C20),A3:A20,E5). Press Enter.
Compare the result with the matching rows in columns A and C. The formula returns the largest matching value strictly below the overall maximum.

Rank Matching Values with LARGE and FILTER
Use this method in Excel versions that support FILTER to find the second-largest value among rows that meet multiple conditions.
Use a LARGE and IF Array Formula in Older Excel
Use this method when FILTER is unavailable and you need to rank values that meet multiple criteria.
Find the Second-Largest Matching Value in WPS Spreadsheet
Use a LARGE and IF array formula in WPS Spreadsheet to rank values that match a category and year.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Arrange the data: Place categories in A3:A20, dates in B3:B20, and numeric values in C3:C20. Enter a category in E5 and a year in F5.
- 3. Enter the formula: Select an empty cell and type =LARGE(IF(($A$3:$A$20=E5)*(YEAR($B$3:$B$20)=F5),$C$3:$C$20),2).
- 4. Confirm and check: Press Ctrl+Shift+Enter to confirm the array formula, then check the result against the matching rows.

Frequently Asked Questions
What is the difference between excluding the maximum and finding the second-largest match?
MAXIFS with a criterion below MAX excludes the overall maximum from the full value range. LARGE with FILTER ranks only the values that meet the conditions.
Does LARGE count duplicate values?
Yes. If the two largest matching values are both 100, LARGE with a rank of 2 returns 100, not the next distinct value.
Why does the second-largest formula return an error?
Check that at least two qualifying rows contain numeric values. Also verify that the date cells used by YEAR contain valid dates and that the ranges cover the same rows.
How do I enter the formula if my Excel version does not support FILTER?
Use the LARGE and IF formula shown above, then press Ctrl+Shift+Enter in older Excel versions to confirm it as an array formula.




