Use XLOOKUP with Wildcards to Find Codes in Text in WPS Spreadsheets
Question details
Find a product code embedded inside longer cell text by using XLOOKUP with wildcard matching instead of a VLOOKUP wildcard formula that failed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a code to look up and a table where that code may appear anywhere within the cell text, and needs to return the matching related value.
- Observed behavior
- A VLOOKUP wildcard formula did not return the expected match; XLOOKUP with match mode 2 can search for the code anywhere within the cell text.
Open your workbook in WPS Office Spreadsheets and confirm that XLOOKUP is available in your version. Identify the lookup range, the return range, and the cell containing the code to search for.
Use XLOOKUP with Match Mode 2 and Asterisk Wildcards
Use this when the lookup code may appear anywhere inside the cell text and you need a reliable wildcard match.
Wildcard matching uses asterisks as placeholders for any number of characters, so the code can be found even when it appears before, after, or between other text in the cell.
Click the cell where you want the matched code or related product value to appear.
Type =XLOOKUP("*"&A17&"*",A14:A100,B14:B100,,2) and press Enter. Replace A17 with the cell containing the code, A14:A100 with the lookup range, and B14:B100 with the return range.
Make sure the lookup range and return range are the same size and cover all rows that may contain the code and the value to return.
Update WPS Office or Use INDEX MATCH as a Fallback
Use this if XLOOKUP is not recognized in your version of WPS Spreadsheets or you need a compatibility formula for older workbooks.
How to Use XLOOKUP Wildcards in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use XLOOKUP with match mode 2 to find codes embedded in longer text. This keeps formulas concise and returns the correct product code without restructuring your data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the lookup table and product codes.
- 2. Select the result cell: Click the cell where the matched code or returned value should appear.
- 3. Enter the XLOOKUP wildcard formula: Type =XLOOKUP("*"&A17&"*",A14:A100,B14:B100,,2) and adjust A17, the lookup range, and the return range to match your worksheet.
- 4. Verify the wildcard match: Press Enter and confirm that WPS Spreadsheets returns the first matching row where the code appears anywhere in the lookup cell.
Frequently Asked Questions
Why does my VLOOKUP wildcard formula fail but XLOOKUP works?
VLOOKUP wildcard matching can fail when ranges, column positions, or match options are not configured correctly. XLOOKUP lets you set match mode 2 directly, which enables wildcard matching and separates the lookup range from the return range.
What does match mode 2 mean in XLOOKUP?
Match mode 2 tells XLOOKUP to use wildcard characters such as asterisks. An asterisk represents any number of characters, so "*"&A17&"*" finds the value in A17 anywhere inside the lookup cell.
Can XLOOKUP search for a code anywhere inside a cell?
Yes. Adding an asterisk before and after the code with "*"&A17&"*" tells XLOOKUP to match the code even when it appears among other text in the cell.
Does WPS Spreadsheets support XLOOKUP?
Current versions of WPS Office Spreadsheets support XLOOKUP. If the function is not recognized, update WPS Office to the latest version or use an INDEX MATCH wildcard formula as a fallback.
How do I find the first matching code when multiple cells contain similar text?
XLOOKUP returns the first match from top to bottom in the lookup range. Make sure the lookup range contains only the rows you want to search, and use unique codes where possible.
