How to Use Wildcards with XLOOKUP for Partial Text Matches
Question details
The user needs to configure XLOOKUP to find partial text matches using wildcards, as the function defaults to exact matches.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Searching for specific substrings within a range of cells using XLOOKUP.
- Observed behavior
- XLOOKUP returns exact matches by default or fails to find records due to trailing spaces and inconsistent punctuation.
Ensure your source data is clean by checking for leading or trailing spaces and inconsistent punctuation, as these hidden characters can prevent lookup functions from returning successful matches.
Enable Wildcard Matching by Setting Match Mode 2
Configure XLOOKUP to recognize wildcards by wrapping your search value in asterisks and changing the match mode argument to 2.
By default, XLOOKUP is designed to search for exact matches. To perform a partial text match, you must explicitly tell the function to process wildcard characters.
Select the cell where you want the result to appear and start typing `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])`.
Concatenate asterisks to your lookup reference to represent any sequence of characters before or after your text. For example, use `"*"&A2&"*"` to find the contents of A2 anywhere within the target text.
Skip the 'if_not_found' argument or set your preferred error message (e.g., "Not found"). Then, set the 'match_mode' argument to `2`, which forces XLOOKUP to apply wildcard matching.
Your finalized formula should look similar to `=XLOOKUP("*"&A2&"*", B2:B10, C2:C10, "Not found", 2)`. Press Enter to return the partial match result.
Clean Source Data to Prevent Match Failures
If wildcard matching still fails, clean the lookup and return arrays to eliminate extra spaces or mismatched punctuation.
Easily Use XLOOKUP and Wildcards in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, allowing you to perform partial text matches and data analysis effortlessly. It provides seamless compatibility with Excel formulas and includes built-in tools for rapid data cleaning.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your target data.
- 2. Select the destination cell: Click on the cell where you want the XLOOKUP result to be displayed.
- 3. Enter the XLOOKUP formula: Type your XLOOKUP formula using `"*"` around your lookup value and `2` for the match mode.
- 4. Apply and analyze: Press Enter to retrieve the result and drag the fill handle down to apply the formula to other rows.
Frequently Asked Questions
Why does XLOOKUP return an error even when I use asterisks?
You might have forgotten to set the 'match_mode' argument to 2. Without this specific setting, XLOOKUP defaults to an exact match (mode 0) and treats the asterisks as literal characters instead of wildcards.
Can I use wildcards to match just one unknown character instead of multiple?
Yes, you can use a question mark (?) instead of an asterisk (*). A question mark represents exactly one character, making it ideal for matching specific patterns like part numbers with minor variations.
How do I search for a literal asterisk or question mark within a cell?
If you need to search for an actual asterisk or question mark in your text, type a tilde (~) immediately before the wildcard character in your lookup value (e.g., `~*` or `~?`). This tells XLOOKUP to treat it as a standard character.
