How to Use Excel Partial Text Lookup with XLOOKUP or SEARCH in WPS Spreadsheets
Question details
User needs an Excel/WPS Spreadsheets formula that returns a price from a lookup table when the item name appears anywhere inside a reference string, without nested IF statements or manual data cleaning.
- Product
- WPS Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has a reference text column and a lookup table with Item and Price columns, and wants to match items by partial text rather than exact cell equality.
- Observed behavior
- Manual data cleaning or nested IF formulas are currently required to match item names inside longer reference strings.
Make sure your lookup table uses distinct item names and that the Item and Price columns are formatted as text and number respectively. Place the formula on the same worksheet or use structured table references such as Table2[Item].
Use LOOKUP and SEARCH to Return the Price for a Partial-Match Item
Best when item names can appear anywhere inside the reference string and you want one compact formula that avoids nested IF statements.
SEARCH returns the character position where each lookup item is found in the reference text, or an error when it is not found.
LOOKUP then reads the last numeric SEARCH result and returns the corresponding price from the Price column.
Format the lookup table with Item and Price columns, then select the data range and press Ctrl+T to create an Excel Table named Table2, or use a named range.
In the result cell, enter =LOOKUP(9^9,SEARCH(Table2[Item],[@Ref]),Table2[Price]) and press Enter.
Use the fill handle to copy the formula to the remaining rows, or enter the formula directly in each result cell in the table.
Use INDEX and MATCH with ISNUMBER and SEARCH for the First Match
Use this alternative when you prefer a MATCH-based formula or need the first matching item instead of the last matching item.
Create Partial Text Lookups in WPS Spreadsheets
You can complete this task directly in WPS Spreadsheets with the same Excel-compatible lookup functions. Enter the partial text formula, reference your table, and return prices without nested IF statements or manual cleaning.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the .xlsx workbook containing the reference text and lookup table.
- 2. Confirm the lookup table structure: Make sure the table has Item and Price columns; select the range, click Insert > Table, and name it Table2 in the Table Tools tab if needed.
- 3. Enter the partial lookup formula: In the result column, type =LOOKUP(9^9,SEARCH(Table2[Item],[@Ref]),Table2[Price]) and press Enter.
- 4. Fill the formula down: Drag the fill handle across the result cells or double-click it in a table to apply the formula to all rows.
- 5. Review and save: Check the returned prices, then click Menu > Save As and choose .xlsx to keep Excel compatibility.
Frequently Asked Questions
Can I use XLOOKUP for partial text matching in WPS Spreadsheets?
Yes, WPS Spreadsheets supports XLOOKUP in modern versions. For a partial value inside a single text range, use =XLOOKUP("*"&item&"*",range,return_range,,2), but to search multiple item names inside a reference string the LOOKUP+SEARCH formula is more direct.
Why does the formula return the last matching item instead of the first?
LOOKUP with 9^9 scans the SEARCH results and returns the last numeric position. If you need the first match, use an INDEX/MATCH array formula with ISNUMBER and SEARCH.
What should I do if the formula returns #N/A?
Check that the reference text and item names are stored as text, that the table names match exactly, and that at least one item name appears in the reference string.
Can I use this formula without converting data to an Excel Table?
Yes, replace Table2[Item] and Table2[Price] with normal ranges such as $A$2:$A$10 and $C$2:$C$10, and reference the current row cell instead of [@Ref].
Does this work in Microsoft Excel and WPS Spreadsheets?
Yes, the LOOKUP and SEARCH functions behave the same in both applications, and WPS Spreadsheets opens and saves .xlsx files without conversion.
