Use XLOOKUP to Match Text After Removing a Prefix in WPS Spreadsheets
Question details
Retrieve a related value when the lookup text includes an extra prefix that is not present in the reference list.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to look up a value from another worksheet or table, but the lookup cell contains a prefix such as "A " before the actual reference code.
- Observed behavior
- An exact XLOOKUP match cannot find the reference value until the unwanted prefix is removed from the lookup text.
Confirm that the reference column contains the clean lookup values, such as 123 rather than A 123. Check that the prefix is written consistently in the source cells, including any spaces after the prefix.
Remove the prefix inside XLOOKUP with SUBSTITUTE
Use this method when every lookup value has the same unwanted prefix and you want to keep the original source value unchanged.
SUBSTITUTE creates a cleaned version of the lookup value before XLOOKUP searches the reference column. This lets you match the underlying code without adding a helper column.
Click the cell where you want WPS Spreadsheets to return the matching value.
Type a formula such as =XLOOKUP(SUBSTITUTE(A11,"A ",""),ReferenceTable[refName],ReferenceTable[x]). Replace A11 with the cell containing the prefixed value, ReferenceTable[refName] with the clean lookup column, and ReferenceTable[x] with the return column.
Press Enter to return the matching value. Drag the fill handle down if you need to apply the formula to additional rows.
Clean the lookup value in a helper column before using XLOOKUP
Use a helper column when prefixes vary, when you need to review cleaned values, or when multiple formulas will use the same cleaned code.
Clean prefixed lookup text and return matching data with WPS Spreadsheets
WPS Spreadsheets lets you combine SUBSTITUTE and XLOOKUP to remove extra text from a lookup value and return the corresponding result from a reference table or another worksheet.
- 1. Open the workbook in WPS Spreadsheets: Open your .xlsx or .xls file and go to the worksheet that contains the prefixed lookup values.
- 2. Identify the source and reference fields: Locate the source cell with text such as A 123, the reference column containing 123, and the column that contains the value to return.
- 3. Build the cleaned lookup formula: In the result cell, enter =XLOOKUP(SUBSTITUTE(A11,"A ",""),ReferenceTable[refName],ReferenceTable[x]) and update the references for your workbook.
- 4. Confirm the returned result: Press Enter and verify that the returned value corresponds to the cleaned reference code.
Frequently Asked Questions
How do I remove more than one possible prefix before an XLOOKUP?
Nest SUBSTITUTE functions in the lookup_value argument, such as =XLOOKUP(SUBSTITUTE(SUBSTITUTE(A11,"A ",""),"B ",""),lookup_array,return_array). If prefixes follow a pattern, a helper column may be easier to review.
Why does XLOOKUP still return an error after I remove the prefix?
Check for extra spaces, hidden characters, or a number-versus-text mismatch. You can remove ordinary spaces with TRIM, for example =XLOOKUP(TRIM(SUBSTITUTE(A11,"A ","")),lookup_array,return_array).
Can XLOOKUP search a reference list on another worksheet?
Yes. Use the other worksheet's ranges in the formula, such as =XLOOKUP(SUBSTITUTE(A11,"A ",""),'Reference Sheet'!A:A,'Reference Sheet'!B:B).
Will SUBSTITUTE change the value in my original lookup cell?
No. When used inside XLOOKUP, SUBSTITUTE only changes the text used during the formula calculation. The original content of the lookup cell remains unchanged.
