Function Problems

How to Use a Cell to Control Multiple Criteria in an Excel FILTER Formula

Nimra Malik Oct 14, 2026 871 views

Question details

The user needs to dynamically control multiple criteria in an Excel FILTER formula using cell references without encountering the #VALUE! error caused by invalid string evaluation.

How to Use a Cell to Control Multiple Criteria in an Excel FILTER Formula

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset dynamically based on multiple criteria controlled by external cell references.
Observed behavior: Using text functions like TEXTJOIN to combine criteria and passing it directly to the include argument results in a #VALUE! error, because the FILTER function requires an array of TRUE/FALSE values, not a combined text string.

Before you start

Ensure your dataset does not contain merged cells or blank rows, and verify that the helper cells you plan to use as criteria references contain data formatted exactly like the values in your source range.

Solution 1Recommended

Use Separate Cells and Boolean Logic (Multiplication) for Criteria

Instead of combining conditions into one string with TEXTJOIN, evaluate each criterion in separate cells and multiply them to create a valid array of TRUE and FALSE values.

The 'include' argument of the FILTER function must evaluate to a Boolean array (TRUE/FALSE). A text string created with TEXTJOIN cannot be parsed as a direct filter condition.

By multiplying separate conditions wrapped in parentheses, Excel applies an 'AND' logic to evaluate multiple criteria correctly.

Set up your criteria helper cells

Designate specific cells to hold your individual filter criteria values outside of your main dataset (e.g., Sheet2!A2, Sheet2!B2, and Sheet2!C2).

Start constructing the FILTER function

Select an empty destination cell, type `=FILTER(`, and select your primary source range (e.g., `Sheet1!A4:BB166`).

Apply Boolean logic for the include argument

Write the conditions wrapped in parentheses and multiplied by each other. For example: `(Sheet1!J4:J166=Sheet2!A2)*(Sheet1!K4:K166=Sheet2!B2)*(Sheet1!C4:C166=Sheet2!C2)`.

Add an 'if_empty' string

Complete the formula by adding a fallback value like `""` to prevent errors if no records match. The final formula looks like: `=FILTER(Sheet1!A4:BB166,(Sheet1!J4:J166=Sheet2!A2)*(Sheet1!K4:K166=Sheet2!B2)*(Sheet1!C4:C166=Sheet2!C2),"")`.

Use Separate Cells and Boolean Logic (Multiplication) for Criteria

Boolean AND Logic: Using the asterisk (*) between conditions applies 'AND' logic, meaning only rows that meet all criteria simultaneously will be returned.

Solution 2

Use the INDIRECT Function for Dynamic Range References

If you need to dynamically change the ranges being evaluated without editing the formula directly, use the INDIRECT function combined with Boolean logic.

Easily Filter Data with Multiple Criteria in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like FILTER, allowing you to seamlessly extract data using multiple criteria. It provides a robust, highly compatible environment to handle complex array formulas efficiently without returning unexpected #VALUE! errors.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the dataset and criteria cells.
2. Enter the FILTER formula: Select the top-left cell where you want the filtered data to appear and type `=FILTER(`.
3. Select array and apply Boolean logic: Select your source array, then define the multiple conditions wrapped in parentheses and linked by the asterisk (*) for AND logic.
4. Press Enter to spill the results: Close the final parenthesis and press Enter. WPS Spreadsheet will automatically spill the filtered results across the adjacent rows and columns.

100% compatibility with Microsoft Excel's dynamic array functions, including FILTER, SORT, and UNIQUELightweight application design ensuring ultra-fast calculation speeds on large datasetsIntuitive and familiar interface for managing complex data sets seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #VALUE! error?

The #VALUE! error typically occurs if the 'include' argument doesn't yield an array of TRUE/FALSE values, if a text string was passed incorrectly, or if the height of your criteria array doesn't match the height of your source array.

Can I use TEXTJOIN to combine criteria for the FILTER function?

No, using TEXTJOIN creates a single text string. Excel cannot evaluate a literal text string directly as an array of TRUE/FALSE logical operations in the FILTER function's include argument.

How do I apply 'OR' logic instead of 'AND' logic with multiple criteria?

To apply 'OR' logic in the FILTER function, use a plus sign (+) between your criteria expressions instead of an asterisk (*). For example: `(Range1=Criteria1)+(Range2=Criteria2)`.

What if no data matches my filter criteria?

You can provide a fallback value by utilizing the third argument `[if_empty]` in the FILTER formula. For instance, putting `"No Results"` at the end of the formula will display that text instead of returning a #CALC! error.

Excel & Data Problems