Find Text Within a List and Return Adjacent Number in WPS Spreadsheets
Question details
The user needs to search for a word inside longer text phrases in a list and return the corresponding number from the adjacent column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a spreadsheet where one column contains longer phrases and the adjacent column contains numeric values, using a partial search term in a separate cell.
- Observed behavior
- Basic lookup functions do not match partial text inside longer phrases unless wildcard matching is enabled, so they may return errors or blank values.
Confirm that the phrase list and numbers are in adjacent columns, and make sure the search term cell is not blank before entering the lookup formulas.
Use XLOOKUP with Wildcards in WPS Spreadsheets
Best for modern WPS Spreadsheets and Excel versions because XLOOKUP supports wildcard matching and handles missing matches cleanly.
Open your workbook in WPS Spreadsheets and click the cell where you want the adjacent number to appear, for example E2.
Type =XLOOKUP("*"&D2&"*",$A$2:$A$50,$B$2:$B$50,"",2) and adjust A2:A50, B2:B50, and D2 to match your actual data ranges and search term cell.
Press Enter to run the formula. WPS returns the adjacent number from column B when the search text is found anywhere in the matching phrase.
Use IFERROR with VLOOKUP Wildcards for Older Versions
Choose this method if your WPS or Excel version does not support XLOOKUP and you need a widely compatible wildcard lookup.
Use WPS Spreadsheets to Look Up Partial Text and Return Adjacent Values
WPS Spreadsheets lets you use familiar lookup functions such as XLOOKUP and VLOOKUP with wildcards, so you can search inside longer phrases and pull the corresponding number from the adjacent column without switching tools.
- 1. Download WPS Office: Go to the WPS Office download page and install WPS Office on your Windows, macOS, Android, or iOS device.
- 2. Open your workbook: Launch WPS Spreadsheets and open the Excel workbook containing the phrase list and adjacent numbers.
- 3. Enter the lookup formula: Click the result cell and enter the recommended XLOOKUP wildcard formula or the IFERROR/VLOOKUP alternative that matches your data ranges.
- 4. Confirm the result: Press Enter to return the adjacent number and verify that the result changes correctly when you update the search text.
Frequently Asked Questions
Can WPS Spreadsheets use wildcards in XLOOKUP?
Yes. Set the match mode argument to 2, as in =XLOOKUP("*"&D2&"*",$A$2:$A$50,$B$2:$B$50,"",2), so the lookup treats the search cell as a wildcard pattern.
Why does my lookup formula return a blank instead of a number?
The formula returns blank when no phrase contains the text in the search cell. Check the search term, remove extra spaces, or apply TRIM to both the search cell and the phrase range.
Can I return a number from a different column instead of the adjacent one?
Yes. For XLOOKUP, replace $B$2:$B$50 with your desired return column. For VLOOKUP, change the column index number, such as 3 for column C.
Does this formula work in older Excel files opened in WPS Office?
Yes. The IFERROR/VLOOKUP wildcard formula is compatible with older .xls and .xlsx workbooks and works in WPS Spreadsheets as well as Excel.
