Function Problems

How to Filter and Match Names Using Multiple Conditions Across Excel Sheets

Adam Davis Oct 15, 2026 871 views

Question details

The user needs to compare names and response data spanning across multiple separate worksheets and output a list of matching or non-matching names to a third worksheet based on at least three specific conditions.

How to Filter and Match Names Using Multiple Conditions Across Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Consolidating and comparing complex data sets across different tabs to generate a targeted list of names meeting multiple criteria.
Observed behavior: The user wants to dynamically return a filtered list of names onto a new worksheet when data across other worksheets meets multiple Boolean logic conditions.

Before you start

Verify that all the data ranges you plan to compare across your worksheets are exactly the same size (e.g., row 2 to 100 in all sheets) to prevent formula size mismatch errors.

Solution 1Recommended

Use the FILTER Function with Boolean Logic

Combine Excel's dynamic FILTER function with Boolean logic (multiplication for AND, addition for OR) to extract names matching complex criteria across sheets.

When comparing criteria across different worksheets, the standard FILTER function can be extended using Boolean logic. By multiplying conditions together, you force Excel to check that ALL conditions are true. If you need partial text matching, nesting the SEARCH function within ISNUMBER is required, as FILTER does not support traditional wildcards.

Select the target destination

Navigate to the third worksheet where you want the matched names to appear and click the top-left cell where the list should start.

Start the FILTER formula

Type `=FILTER(` and select or type the array of names you want to return, for example: `Sheet1!A2:A100,`.

Add exact match conditions (AND logic)

Open a parenthesis for your first condition, select the criteria range, and set the condition. Multiply it by the next condition. Example: `(Sheet2!B2:B100="Yes") * (Sheet3!C2:C100>50)`.

Add partial text match conditions

If your third condition requires matching part of a word, use ISNUMBER and SEARCH. Multiply it by your previous conditions: `* ISNUMBER(SEARCH("keyword", Sheet2!D2:D100))`.

Execute the dynamic array

Close the final parentheses so your formula looks like `=FILTER(Sheet1!A2:A100, (Cond1)*(Cond2)*(Cond3), "No Match")` and press Enter. The names will automatically spill down the column.

Use the FILTER Function with Boolean Logic

Handling Empty Results: Adding a third argument to the FILTER function, like "No Match", ensures that if no names meet all three conditions, your spreadsheet returns a clean message instead of a #CALC! error.

Advanced Spreadsheet Features

Filter Data Across Sheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array formulas, allowing you to seamlessly filter, match, and consolidate data across multiple worksheets with high efficiency and no performance lag.

1. Open your multi-sheet workbook: Launch WPS Spreadsheet and open the file containing the data you need to cross-reference.
2. Navigate to your summary sheet: Click on the tab of the worksheet where you want the filtered data to be generated.
3. Input the dynamic formula: Enter your `=FILTER()` formula using the asterisk `*` operator to chain your multiple sheet conditions together.
4. Review automatic spill results: Press Enter. WPS Spreadsheet will automatically calculate and spill the matched names into the adjacent cells without needing legacy Ctrl+Shift+Enter arrays.

Native support for advanced dynamic array functions like FILTER, UNIQUE, and SORTSeamless compatibility with Microsoft Excel (.xlsx) file formatsIntuitive multi-tab management for complex cross-sheet data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER formula returning a #VALUE! error when comparing multiple sheets?

This error typically occurs if the arrays or ranges you are comparing are not the exact same size. Ensure that the row numbers match perfectly across all referenced sheets (e.g., Sheet1!A2:A100 and Sheet2!B2:B100).

How can I filter names using OR logic instead of AND logic?

To use OR logic where only one of the conditions needs to be true, use a plus sign (+) instead of an asterisk (*) between your condition statements in the FILTER function. For example: `(Sheet2!B2:B100="Yes") + (Sheet3!C2:C100>50)`.

Does the FILTER function support wildcard characters?

The FILTER function does not directly support standard wildcards like the asterisk (*) or question mark (?). To perform a wildcard or partial text search, you must nest the SEARCH function inside the ISNUMBER function as one of your condition arguments.

Excel & Data Problems