How to Use XLOOKUP and FILTER for Multiple Criteria in Excel
Question details
The user needs to search and extract task assignments for multiple people without duplicating rows, using XLOOKUP and FILTER functions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing task assignments where a single task might be assigned to multiple people using comma-separated initials in one cell.
- Observed behavior
- Duplicating rows disrupts task numbers and reporting, requiring a dynamic formula solution to extract records based on partial text matches.
Ensure you are using a modern spreadsheet application that supports dynamic array functions like XLOOKUP and FILTER, such as Microsoft 365, Excel 2021, or the latest version of WPS Office.
Use XLOOKUP with Wildcards for Comma-Separated Values
Best for finding the first matching task when initials are combined in a single cell separated by commas.
When multiple initials are stored in a single cell (e.g., "AB1, CD1, JB1"), a standard exact match will fail. By using wildcard characters (asterisks) and setting the match mode to 2, XLOOKUP can search for a specific substring within the cell.
Click on the cell where you want the lookup result to appear.
Type the formula: =XLOOKUP("*JB1*",'Data Sheet'!J:J,'Data Sheet'!B:B,"None found",2). Replace "JB1" with the specific initials you are searching for.
Press Enter. The number 2 at the end of the formula tells Excel to use wildcard character matching, allowing it to find "JB1" even if it is surrounded by other text.
Use FILTER and SEARCH to Return All Matching Tasks
Ideal when you need to extract a complete list of all tasks assigned to a specific person from a column containing multiple initials.
Use XLOOKUP or FILTER for Exact Matches
Use this method if the assignment column contains exactly one person's initials per cell without commas.
Master Advanced Formulas Easily with WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP and FILTER, allowing you to manage complex task assignments and reports effortlessly without paying for expensive subscriptions.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your task assignment data.
- 2. Start your formula: Select the target cell and type =XLOOKUP( or =FILTER( to begin.
- 3. Follow the syntax guide: Use the on-screen formula tooltips to input your search value, lookup array, and return array accurately.
- 4. Get instant results: Press Enter to instantly populate your data dynamically without duplicating rows.
Frequently Asked Questions
Why is my XLOOKUP wildcard formula returning an error?
Ensure you have set the match mode to 2 at the end of your XLOOKUP formula (e.g., ... ,2)). Without this specific argument, XLOOKUP defaults to an exact match and will not recognize wildcard characters like asterisks (*).
Can I use FILTER to search for multiple different initials at once?
Yes, you can combine multiple conditions within the FILTER function's include argument. Use the plus sign (+) for OR logic (e.g., matching "JB1" OR "CD1") and the asterisk (*) for AND logic.
What happens if the FILTER function finds no matches?
By default, the FILTER function will return a #CALC! error if no records match your criteria. You can prevent this by filling in the optional third argument [if_empty] with a custom text string, such as "Not found".
