Function Problems

Use LARGE and MAXIFS to Find the Second-Largest Value in Excel

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Find the second-largest value that meets criteria or exclude the overall maximum from a MAXIFS calculation.

Find the Second-Largest Value with LARGE and MAXIFS in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the ranges

In your worksheet, put values in C3:C20, categories in A3:A20, and the category to match in E5.

2
Enter the MAXIFS formula

Select an empty cell and enter =MAXIFS(C3:C20,C3:C20,"<"&MAX(C3:C20),A3:A20,E5). Press Enter.

3
Check the result

Compare the result with the matching rows in columns A and C. The formula returns the largest matching value strictly below the overall maximum.

Exclude the Overall Maximum with MAXIFS
Ties and empty matches: This formula excludes every value equal to the overall maximum. MAXIFS can return 0 when no cells meet the criteria, so check the matching rows if the result is unexpected.
WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 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. 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. 4. Confirm and check: Press Ctrl+Shift+Enter to confirm the array formula, then check the result against the matching rows.
Rank values using multiple worksheet criteriaOpen and edit Microsoft Excel XLSX and XLS filesWork with familiar spreadsheet formulas and cell references
microsoft office alternative - wps office

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.