Function Problems

How to Use Excel FILTER with Multiple Criteria and LARGE

Maira Mehtab Sep 22, 2026 870 views

Question details

The user is trying to combine the Excel FILTER function using multiple criteria with the LARGE function to extract the top values, but the formula is returning a #NUM! error.

Product: Excel
Device & OS: not provided
Scenario: Extracting the top N largest values from a dataset based on multiple specific conditions.
Observed behavior: The combined formula fails to return the expected results and instead produces a #NUM! error, indicating an issue with criteria matching or array dimensions.

Before you start

Verify that your criteria ranges have exactly the same dimensions as your data array, and ensure there are enough matching records in your dataset to satisfy the number requested by the LARGE function.

Solution 1Recommended

Combine FILTER and LARGE Functions Correctly

Apply the FILTER function using Boolean logic to isolate records matching multiple criteria, then wrap it in the LARGE function to extract the specific top values.

When using multiple criteria in the FILTER function, you must use multiplication (*) for AND logic. The resulting array is then passed to the LARGE function to find the kth largest value.

Set up the FILTER function

Select the cell where you want the result. Type the FILTER function using multiplication for multiple criteria: `=FILTER(data_range, (criteria_range1="Condition1") * (criteria_range2="Condition2"))`.

Wrap with the LARGE function

Modify the formula to include LARGE by wrapping the FILTER function. Specify the position 'k' you want to retrieve. For the largest value, use 1: `=LARGE(FILTER(data_range, (criteria_range1="Condition1") * (criteria_range2="Condition2")), 1)`.

Extract multiple top values (Optional)

If you want to extract the top 3 values simultaneously, replace the 'k' argument with an array constant: `=LARGE(FILTER(...), {1,2,3})` and press Enter.

Troubleshooting #NUM! Errors: A #NUM! error occurs if there are fewer matching records than the 'k' value requested (e.g., asking for the 3rd largest value when only 2 records match the criteria). Ensure your filtered array has enough results.

Efficient Data Analysis

Extract Top Values Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and LARGE, allowing you to build complex multi-criteria data extractions smoothly. It provides a seamless experience for handling advanced formulas.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
2. Select the target cell: Click on an empty cell where you want the top results to be displayed.
3. Input the combined formula: Enter the formula `=LARGE(FILTER(A2:A100, (B2:B100="Value1")*(C2:C100="Value2")), 1)` and press Enter to instantly calculate the result.

Fully compatible with Microsoft Excel formats (.xlsx) and formulas.Supports dynamic arrays and modern functions like FILTER, SORT, and UNIQUE.Lightweight, fast, and completely free to use for everyday data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #CALC! or #NUM! error?

These errors occur when the FILTER function finds no records matching your criteria, or the number of valid matches is less than the position (k) requested in the LARGE function. Double-check your criteria and ensure the data exists.

How do I use 'OR' logic instead of 'AND' logic with the FILTER function?

To use 'OR' logic, replace the multiplication sign (*) with a plus sign (+) in your criteria arguments. For example: `(range1="Condition1") + (range2="Condition2")`.

How can I avoid errors when no data matches the FILTER criteria?

You can use the 'if_empty' argument built into the FILTER function, or wrap your entire formula in the IFERROR function, like this: `=IFERROR(LARGE(FILTER(...), 1), "No matches found")`.

Excel & Data Problems