How to Search an Excel Table Using Multiple Criteria with Formulas
Question details
The user needs a practical formula method to search a large data table using multiple criteria and return matching names.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting specific names from a large dataset based on two or more distinct conditions, including handling non-English characters.
- Observed behavior
- Requires a function or combination of functions that accurately filters the database, evaluates multiple parameters simultaneously, and outputs the exact corresponding names without errors.
Ensure your dataset is organized in a clear tabular format without merged cells, and explicitly identify the columns that will serve as your search criteria and return values.
Use the FILTER Function with Boolean Logic (Recommended)
The FILTER function is the easiest and most robust method for extracting data based on multiple criteria in newer versions of spreadsheet software.
The FILTER function allows you to apply Boolean logic to arrays. By multiplying conditions together with an asterisk (*), you enforce an 'AND' criteria setup, meaning only rows that meet all applied conditions will be returned. This handles non-English data seamlessly.
Click on the empty cell where you want the first matching name to appear. Ensure there is enough empty space below it for the results to spill.
Type the formula: =FILTER(return_array, (criteria1) * (criteria2), "No results found").
Replace 'return_array' with the specific column range containing the names you want to extract (e.g., A2:A100).
Replace the criteria sections with your actual conditions. For example: (B2:B100="Condition1") * (C2:C100="Condition2").
Press Enter. The formula will automatically generate the list of matching names down the column.
Use XLOOKUP with Multiple Criteria for a Single Match
XLOOKUP is highly efficient if you only need to return the first matching name that meets all your criteria.
Use INDEX and MATCH with Array Formulas (For Older Versions)
If you are using an older version of Excel that does not support FILTER or XLOOKUP, use the INDEX and MATCH combination.
Search Complex Data Tables Using WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and XLOOKUP, allowing you to seamlessly process multiple search criteria with modern formulas.
- 1. Open your data file: Launch WPS Spreadsheet and open your dataset containing the multiple criteria columns.
- 2. Select a target cell: Click on an empty cell where you want the search results to populate.
- 3. Apply the FILTER formula: Type =FILTER(A2:A100, (B2:B100="Criteria1") * (C2:C100="Criteria2"), "No Match") and press Enter to instantly extract the data.
Frequently Asked Questions
How do I use 'OR' logic instead of 'AND' logic when searching with multiple criteria?
To use 'OR' logic, use the plus sign (+) instead of the multiplication sign (*) between your criteria arrays. For example, (A1:A10="Red") + (B1:B10="Blue") will return rows that match either condition.
Why does my INDEX MATCH array formula return a #VALUE! error?
This typically occurs in older spreadsheet versions if the formula isn't evaluated as an array formula. Ensure you highlight the formula in the formula bar and press Ctrl + Shift + Enter.
Can I search for partial text matches using multiple criteria?
Yes, you can combine the SEARCH or FIND function within your criteria arrays to locate partial strings. Replace the standard equality check with ISNUMBER(SEARCH("text", range)).
