Excel Formula to Return the Ten Largest Values Matching Multiple Conditions
Question details
The user needs a formula that returns the ten largest values from a range only when multiple conditions are satisfied.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Values are stored on the November sheet, and the formula must select values from column G while excluding rows where column M is Y or column H is No.
- Observed behavior
- The desired result is a ranked list of matching values, with the largest value returned first and subsequent values returned by copying the formula down.
Confirm that columns G, H, and M use matching row ranges and that the status text is exactly Y and No. Select ten output cells in one column if you want the ten largest matching values.
Use LARGE with IF and multiplication for multiple conditions
Use this array formula when every condition must be true and you want to return matching values from largest to smallest.
Multiplying the logical tests makes them work as an AND condition: only rows where column M is not Y and column H is not No are passed to LARGE. ROW(A1) returns the largest value, and copying the formula down changes it to ROW(A2), ROW(A3), and so on.
In older Excel versions, confirm the formula with Ctrl+Shift+Enter. Microsoft 365 and other supported dynamic-array versions normally accept it with Enter.
In the first result cell, enter =IFERROR(LARGE(IF((November!$M$2:$M$20<>"Y")*(November!$H$2:$H$20<>"No"),November!$G$2:$G$20),ROW(A1)),"").
Press Enter in Microsoft 365, dynamic-array Excel, or supported newer versions. In older Excel versions, press Ctrl+Shift+Enter instead of Enter.
Drag the fill handle down through ten result rows. ROW(A1) will increment to ROW(A2), ROW(A3), and so on, returning the next-largest matching value in each row.
Return the Ten Largest Matching Values in WPS Spreadsheet
WPS Spreadsheet supports the same LARGE, IF, and logical multiplication approach for ranking values that meet multiple conditions. Enter the formula, confirm it according to your WPS version, and fill it down to create the ranked list.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and go to the sheet where you want the results.
- 2. Enter the formula: Select the first result cell and enter =IFERROR(LARGE(IF((November!$M$2:$M$20<>"Y")*(November!$H$2:$H$20<>"No"),November!$G$2:$G$20),ROW(A1)),"").
- 3. Confirm the formula: Press Enter in supported newer WPS Spreadsheet versions. If your version requires legacy array formulas, press Ctrl+Shift+Enter.
- 4. Fill the results: Drag the fill handle down ten rows to return the ten largest values that meet both conditions.
Frequently Asked Questions
Why does the formula multiply the two conditions?
Multiplication applies an AND relationship to the tests. A row is included only when November!$M$2:$M$20 is not Y and November!$H$2:$H$20 is not No.
How do I return all ten values?
Enter the formula in the first result cell and copy it down ten rows. ROW(A1) changes as it is copied, so LARGE returns the first, second, third, and later ranked values.
Why does the formula show a blank cell?
IFERROR changes the error produced when no additional matching value exists into an empty string. Check the criteria text and source ranges if blanks appear before the expected end of the list.
Do I need Ctrl+Shift+Enter for this formula?
Older Excel versions generally require Ctrl+Shift+Enter for this array formula. Microsoft 365, dynamic-array Excel, and supported newer spreadsheet versions usually accept it with Enter.
